ReaderMX.
开源软件联盟PostgreSQL分会

PostgreSQL有趣的插件和工具

开开源软件联盟PostgreSQL分会约 6 分钟

Image

虚拟索引hypopg

该扩展有助于了解特定索引是否可以提高问题查询的性能。虚拟索引并不是真实存在的索引，因此不耗费CPU、磁盘或其他资源，可以有效验证索引是否有效。

创建表并插入测试数据。

  1. CREATE TABLE hypo (id integer, val text);
  2. INSERT INTO hypo SELECT i,'line '|| i FROM generate_series(1,100000) i ;
  3. VACUUM ANALYZE hypo ;

查看当前表没有任何索引的执行计划，并使用了普通顺序扫描。

  1. EXPLAIN SELECT val FROM hypo WHERE id =1;
  2.                        QUERY PLAN
  3. --------------------------------------------------------
  4. SeqScan on hypo  (cost=0.00..1791.00 rows=1 width=14)
  5. Filter:(id =1)
  6. (2 rows)

用hypopg_create_index()函数创建一个虚拟索引。

  1. SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)');
  2.  indexrelid |      indexname
  3. ------------+----------------------
  4. 18284|<18284>btree_hypo_id
  5. (1 row)

再次EXPLAIN查看是否会用到这个btree索引。

  1. EXPLAIN SELECT val FROM hypo WHERE id =1;
  2.                                     QUERY PLAN
  3. ----------------------------------------------------------------------------------
  4. IndexScanusing<18284>btree_hypo_id on hypo  (cost=0.04..8.06 rows=1 width=10)
  5. IndexCond:(id =1)
  6. (2 rows)

检查虚拟索引并未实际使用到，对现环境没有影响。

  1. EXPLAIN ANALYZE SELECT val FROM hypo WHERE id =1;
  2.                                             QUERY PLAN
  3. ---------------------------------------------------------------------------------------------------
  4. SeqScan on hypo  (cost=0.00..1791.00 rows=1 width=10)(actual time=0.046..46.390 rows=1 loops=1)
  5. Filter:(id =1)
  6. RowsRemovedbyFilter:99999
  7. Planning time:0.160 ms
  8. Execution time:46.460 ms
  9. (5 rows)

orafce

orafce英文全称Oracle’s compatibility functions and packages。可以在PostgreSQL上使用Oracle的特殊函数和包，并且兼容Oracle的部分语法、数据类型、函数、字典表等。

通过orafce可以和Oracle兼容的功能。

数据类型

ItemOverview
VARCHAR2变长字符数据类型
NVARCHAR2变长国家字符数据类型
DATE存储日期和时间的数据类型

SQL 查询

ItemOverview
DUAL table系统提供的虚拟表

SQL 函数

  • 数学运算函数

ItemOverview
BITAND执行位与操作
COSH计算一个数字的双曲余弦
SINH计算一个数字的双曲正弦
TANH计算一个数字的双曲正切

  • 字符串函数

ItemOverview
INSTR返回子字符串在字符串中的位置
LENGTH以字符数为单位返回字符串的长度
LENGTHB以字节数为单位返回字符串的长度
LPAD用字符序列左填充字符串到指定长度
LTRIM从字符串的开头删除指定的字符
NLSSORT返回一个字节字符串，用于根据区域设置对语言排序序列中的字符串进行排序
REGEXP_COUNT在字符串中搜索正则表达式，并返回匹配次数
REGEXP_INSTR返回字符串中模式匹配所在的起始或结束位置
REGEXP_LIKE判断字符串是否符合正则表达式的规则
REGEXP_SUBSTR返回与函数调用中指定的模式匹配的字符串
REGEXP_REPLACE替换匹配POSIX正则表达式的子字符串
RPAD用字符序列将字符串右填充到指定长度
RTRIM从字符串的末尾删除指定字符
SUBSTR使用指定位置和长度的字符提取字符串的一部分
SUBSTRB使用字节来指定位置和长度提取字符串的一部分

  • 日期/时间函数

ItemOverview
ADD_MONTHS为日期添加月份
DBTIMEZONE返回数据库时区的值
LAST_DAY返回指定日期所在月份的最后一天
MONTHS_BETWEEN返回两个日期之间的月数
NEXT_DAY用于计算给定日期的下一天
ROUND对日期进行四舍五入处理
SESSIONTIMEZONE返回会话的时区
SYSDATE返回系统日期
TRUNC截断日期

  • 数据类型格式化函数

ItemOverview
TO_CHAR将值转换为字符串
TO_DATE按照指定的格式将字符串转换为日期
TO_MULTI_BYTE将单字节字符串转换为多字节字符串
TO_NUMBER按照指定的格式将值转换为数字
TO_SINGLE_BYTE将多字节字符串转换为单字节字符串

  • 条件表达式

ItemOverview
DECODE比较值，如果它们匹配，则返回相应的值
GREATEST返回一个或多个表达式列表中的最大值
LEAST返回一个或多个表达式列表中的最小值
LNNVL计算值是否为false或未知
NANVL当值不是数字(NaN)时返回替代值
NVL当值为NULL时返回替代值
NVL2根据值是否为NULL返回替代值

  • 聚合函数

ItemOverview
LISTAGG将指定列的值按照指定的分隔符组合成一个字符串
MEDIAN计算一组值的中位数

  • 返回内部信息的函数

ItemOverview
DUMP返回值的内部信息

SQL 操作

ItemOverview
Datetime operatorDATE类型的日期时间操作符

程序包

ItemOverview
DBMS_ALERT向多个会话发送警报
DBMS_ASSERT验证输入值的属性
DBMS_OUTPUT向客户端发送消息
DBMS_PIPE创建用于会话间通信的管道
DBMS_RANDOM生成随机数
DBMS_UTILITY提供各种实用程序
UTL_FILE用于操作系统的文本文件操作

pg_top

pg_top是PostgreSQL的“top”。与top类似可以监视PostgreSQL进程。还可以查看进程当前正在运行的SQL语句。另外还可以：

查看进程当前正在运行的SQL语句。

查看当前运行的SELECT语句的查询计划。

进程持有的视图锁。

查看每个进程的I/O统计信息。

查看下行节点的复制统计信息。

Image

可以监控到主机的负载、CPU、内存、SWAP、PG的进程数（总数、活跃数，空闲数等），同样还能显示具体的进程信息（PID、DB用户名、资源占用情况等）。

Image

“Q”键并输入PID可以查看当前执行的SQL。

Image

“E”键（EXPLAIN）或A键（EXPLAIN ANALYZE），然后再输入具体的PID，可以查看进程当前运行SQL的执行计划。

Image

‘L’键并输入PID可以查看当前数据库LOCK信息。

plprofiler

查找PL/pgSQL函数和存储过程中的性能问题可能很困难，尤其是在代码嵌套的情况下。通过系统视图或扩展视图(如pg_stat_activity或pg_stat_statements)来辅助分析函数和存过中的性能问题也是有所局限。

plprofiler扩展可用于快速识别最耗时的函数，然后向下钻取查找其中的单个语句，并生成html报表。报表以火焰图形式展示函数调用堆栈、耗时占比，还可以查看函数中每个SQL的位置、执行次数、最长执行时间、总时间等。

创建扩展后，使用plprofiler调用函数，从后端收集本地数据来创建HTML报告。

  1. plprofiler run --command "SELECT tpcb(1, 2, 3, -42)"--output tpcb-test1.html

使用plprofiler生成的报表示例。

Image

在页面顶部突出的是两个函数tpcb_fetch_balance()和它的调用者tpcb_upd_accounts()。可以看到它实际上占了PL/pgSQL函数内部总执行时间的99%以上。

Image

为了进一步研究这个函数，报告中单击(show)链接显示详细信息，我们可以看到函数的源代码和每一行所花费的执行时间。

PGTune工具

PGTune可以根据给定硬件配置的最大性能计算PostgreSQL配置。对于初学者来说可以快速地来配置数据库参数。但它不是PostgreSQL优化设置的灵丹妙药。许多设置不仅取决于硬件配置，还取决于数据库的大小、客户端的数量和查询的复杂性。只有考虑到所有这些参数，才能对数据库进行最佳配置。

Image

点击“生成”按钮后将计算结果。

Image

源代码下载：

https://hypopg.readthedocs.io/en/rel1_stable/usage.html

https://github.com/orafce/orafce

https://pgtune.leopard.in.ua

https://github.com/markwkm/pg_top

https://github.com/bigsql/plprofiler

ImageImage

KEEP THE THREAD GOING

更多来源文章