PG几个有趣的插件和工具介绍

PGTune工具

https://pgtune.leopard.in.ua

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

img

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

img

虚拟索引hypopg

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

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

创建表并插入测试数据。

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

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

EXPLAIN SELECT val FROM hypo WHERE id = 1;
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on hypo  (cost=0.00..1791.00 rows=1 width=14)
   Filter: (id = 1)
(2 rows)

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

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

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

EXPLAIN SELECT val FROM hypo WHERE id = 1;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using <18284>btree_hypo_id on hypo  (cost=0.04..8.06 rows=1 width=10)
   Index Cond: (id = 1)
(2 rows)

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

EXPLAIN ANALYZE SELECT val FROM hypo WHERE id = 1;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Seq Scan on hypo  (cost=0.00..1791.00 rows=1 width=10) (actual time=0.046..46.390 rows=1 loops=1)
   Filter: (id = 1)
   Rows Removed by Filter: 99999
 Planning time: 0.160 ms
 Execution time: 46.460 ms
(5 rows)

orafce

https://github.com/orafce/orafce

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

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

数据类型

SQL 查询

SQL 函数

• 数学运算函数

• 字符串函数

• 日期/时间函数

• 数据类型格式化函数

• 条件表达式

• 聚合函数

• 返回内部信息的函数

SQL 操作

程序包

pg_top

https://github.com/markwkm/pg_top

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

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

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

进程持有的视图锁。

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

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

pg_top full command line

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

pg_top Current Query

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

pg_top Query Plan

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

pg_top Locks Acquired

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

plprofiler

https://github.com/bigsql/plprofiler

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

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

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

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

使用plprofiler生成的报表示例。

img

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

img

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

参考

https://pgfans.cn/a/2253