【PGCCC】pg_show_plans：显示所有正在运行的语句的查询计划

PostgreSQL 扩展可显示所有当前正在运行的 SQL 语句的查询计划。查询计划可以以多种格式显示，例如JSON或YAML。

此扩展在共享内存中创建哈希表。哈希表不可调整大小，因此一旦填满，就无法添加新计划。

安装

支持 PostgreSQL 版本 12 及更新版本。

在继续之前安装 PostgreSQL。确保有pg_config二进制文件，这些通常包含在-dev和-devel包中。

git clone https://github.com/cybertec-postgresql/pg_show_plans.git
cd pg_show_plans
make
make install

配置

添加pg_show_plans到shared_preload_libraries内postgresql.conf：

shared_preload_libraries = 'pg_show_plans'

重新启动服务器并调用CREATE EXTENSION pg_show_plans;：

postgresql=# CREATE EXTENSION pg_show_plans;
CREATE EXTENSION
postgresql=#

用法

查看查询计划：

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 |

基准

pgbench -c 10 -j 3 -t 5000 -S普通的 PostgreSQL 16.1：

tps = 193655.084802 (without initial connection time)
tps = 200890.346014 (without initial connection time)
tps = 199931.223659 (without initial connection time)

pgbench -c 10 -j 3 -t 5000 -SPostgreSQL16.1版本：pg_show_plans​2.1.0

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 = texttext：查询计划输出格式， 、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。

• evel：查询嵌套级别。顶层为 0。例如，如果您执行一个简单的 select 查询，则此查询的计划级别为 0。如果您执行调用 select
  查询的函数，则级别 0 是该函数的计划，级别 1 是该函数调用的 select 查询的计划。

• userid：运行查询的用户 ID。

• dbid：查询运行的数据库 ID。

• plan：查询计划。
  #PG培训#PG考试#postgresql培训#postgresql考试#postgresql认证