二月 22, 2024
摘要：pg_show_plans扩展可以显示当前所有正在运行的 SQL 语句的查询计划。查询计划可以以多种格式显示，如JSON或YAML。
目录
pg_show_plans 使用 PostgreSQL 核心中的一组钩子来提取所有相关信息。然后，这些计划会被存储在共享内存中，并通过视图公开。 这使得访问这些查询计划会变得很方便。
此扩展会在共享内存中创建一个哈希表。该哈希表的大小无法调整，因此，一旦填满，就无法再添加新计划了。
安装
可以使用 PGXS 机制（推荐），或在源代码树中编译，来安装扩展。支持 PostgreSQL 版本 12 及更高版本。
要激活该扩展，您必须在postgresql.conf文件中的shared_preload_libraries参数添加pg_show_plans：
shared_preload_libraries = 'pg_show_plans'
重新启动服务器，然后调用CREATE EXTENSION pg_show_plans：
postgres=# CREATE EXTENSION pg_show_plans;
CREATE EXTENSION
postgres=#
用法
若要查看查询计划，请执行以下操作：
testdb=# SELECT * FROM pg_show_plans;
pid | level | userid | dbid | plan
-------+-------+--------+-------+-----------------------------------------------------------------------
11473 | 0 | 10 | 16384 | Function Scan on pg_show_plans (cost=0.00..10.00 rows=1000 width=56)
11504 | 0 | 10 | 16384 | Function Scan on print_item (cost=0.25..10.25 rows=1000 width=524)
11504 | 1 | 10 | 16384 | Result (cost=0.00..0.01 rows=1 width=4)
(3 rows)
若要获取查询计划，并查看相应的查询语句，请执行以下操作：
testdb=# \x
Expanded display is on.
testdb=# SELECT * FROM pg_show_plans_q;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------
pid | 11473
level | 0
plan | Sort (cost=72.08..74.58 rows=1000 width=80) +
| Sort Key: pg_show_plans.pid, pg_show_plans.level +
| -> Hash Left Join (cost=2.25..22.25 rows=1000 width=80) +
| Hash Cond: (pg_show_plans.pid = s.pid) +
| Join Filter: (pg_show_plans.level = 0) +
| -> Function Scan on pg_show_plans (cost=0.00..10.00 rows=1000 width=48) +
| -> Hash (cost=1.00..1.00 rows=100 width=44) +
| -> Function Scan on pg_stat_get_activity s (cost=0.00..1.00 rows=100 width=44)
query | SELECT p.pid, p.level, p.plan, a.query FROM pg_show_plans p +
| LEFT JOIN pg_stat_activity a +
| ON p.pid = a.pid AND p.level = 0 ORDER BY p.pid, p.level;
-[ RECORD 2 ]-----------------------------------------------------------------------------------------
pid | 11517
level | 0
plan | Function Scan on print_item (cost=0.25..10.25 rows=1000 width=524)
query | SELECT * FROM print_item(1,20);
-[ RECORD 3 ]-----------------------------------------------------------------------------------------
pid | 11517
level | 1
plan | Result (cost=0.00..0.01 rows=1 width=4)
query |
基准测试
在普通的 PostgreSQL 16.1 中运行 pgbench：
$ pgbench -c 10 -j 3 -t 5000 -S
tps = 193655.084802 (without initial connection time)
tps = 200890.346014 (without initial connection time)
tps = 199931.223659 (without initial connection time)
在带有pg_show_plans的 PostgreSQL 16.1 中运行 pgbench，扩展版本为 2.1.0：
$ pgbench -c 10 -j 3 -t 5000 -S
tps = 166564.507102 (without initial connection time)
tps = 172814.245424 (without initial connection time)
tps = 174658.455390 (without initial connection time)
总体上大约有 15% 的性能损失。
参考
GUC 变量
pg_show_plans.plan_format = text：查询计划输出格式，可设为text、json、yaml和xml。pg_show_plans.max_plan_length = 16384：查询计划的最大长度（以字节为单位）。此值会影响扩展申请的共享内存量，如果该值设置过高，服务器可能会无法启动。pg_show_plans.is_enabled = true：通过设置此变量，来启用或禁用扩展。
默认值显示在 “=” 符号之后。
视图
pg_show_plans：定义为SELECT * FROM pg_show_plans();，以方便查询。pg_show_plans_q：与pg_show_plans相同，但它多了一列，其中包含了相应的查询字符串。
函数
pg_show_plans()：显示查询计划：pid：运行查询的服务器进程 ID。level：查询嵌套层级。最高层级为 0。例如，如果执行简单的 SELECT 查询，则此查询计划的层级为 0。 如果执行一个调用 SELECT 查询的函数，则函数的计划层级是 0，函数调用的 SELECT 查询的计划层级是 1。userid：运行查询的用户 ID。dbid：运行查询的数据库 ID。plan：查询计划。