pg_show_plans: 查看 PostgreSQL 中运行的查询的执行计划

二月 22, 2024

摘要pg_show_plans扩展可以显示当前所有正在运行的 SQL 语句的查询计划。查询计划可以以多种格式显示,如JSONYAML

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:查询计划输出格式,可设为textjsonyamlxml
  • 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:查询计划。