pg_store_plans: 跟踪 PostgreSQL 查询的执行计划

二月 20, 2024

摘要pg_store_plans插件提供了一种方法,可跟踪服务器执行的所有 SQL 语句的执行计划统计信息。

该插件需要通过在postgresql.conf中设置 shared_preload_libraries 来加载,因为它需要额外的共享内存。这意味着需要重新启动服务器才能添加或删除插件。pg_store_plans要求 GUC 变量compute_query_id设为 “on” 或 “auto”。如果设置为 “no”,则pg_store_plans会以静默方式禁用。

pg_store_plans 视图

该插件收集的统计信息,可通过查询pg_store_plans系统视图获得。视图中包含的每一行,对应不同的数据库 ID、用户 ID 和查询 ID 的组合。表 1 中描述了该视图中的列。

表 1. pg_store_plans

名称 类型 引用 描述
userid oid pg_authid.oid 执行该语句的用户的 OID
dbid oid pg_database.oid 执行该语句所在的数据库的 OID
queryid bigint 内核生成的查询 ID。如果 compute_query_id 设置为 “no”,则 pg_store_plan 会静默禁用。这可用作同pg_stat_statements的连接键。
planid bigint 计划的哈希码,根据计划的规范化表示形式计算得出。
plan text 一个形式化计划的文本。格式由配置参数pg_store_plans.plan_format指定。
calls bigint 执行次数
total_time double precision 该语句使用该计划花费的总时间,以毫秒计
rows bigint 该语句使用该计划检索或影响的行总数
shared_blks_hit bigint 该语句使用该计划造成的共享块缓冲命中总数
shared_blks_read bigint 该语句使用该计划读取的共享块的总数
shared_blks_dirtied bigint 该语句使用该计划弄脏的共享块的总数
shared_blks_written bigint 该语句使用该计划写入的共享块的总数
local_blks_hit bigint 该语句使用该计划造成的本地块缓冲命中总数
local_blks_read bigint 该语句使用该计划读取的本地块的总数
local_blks_dirtied bigint 该语句使用该计划弄脏的本地块的总数
local_blks_written bigint 该语句使用该计划写入的本地块的总数
temp_blks_read bigint 该语句使用该计划读取的临时块的总数
temp_blks_written bigint 该语句使用该计划写入的临时块的总数
blk_read_time double precision 该语句使用该计划花在读取块上的总时间,以毫秒计(如果 track_io_timing 被启用,否则为零)
blk_write_time double precision 该语句使用该计划花在写入块上的总时间,以毫秒计(如果 track_io_timing 被启用,否则为零)
temp_blk_read_time double precision 该语句使用该计划花在读取临时文件块上的总时间,以毫秒计(如果 track_io_timing 被启用,否则为零)
temp_blk_write_time double precision 该语句使用该计划花在写入临时文件块上的总时间,以毫秒计(如果 track_io_timing 被启用,否则为零)
first_call timestamp with time zone 该查询使用该计划的首次调用时间戳。
last_call timestamp with time zone 该查询使用该计划的最近调用时间戳。

此视图和函数pg_store_plans_reset pg_store_plans,以及其他辅助函数,仅在由CREATE EXTENSION安装pg_store_plans的数据库中可用。但是,只要将pg_store_plans插件加载到服务器上,就会跟踪服务器中所有数据库的统计信息,而不管视图是否存在。

出于安全原因,不允许非超级用户查看其他用户执行的查询的计划表示形式、queryid 或 planid。

queryid的计算值用于识别源查询,类似于pg_stat_statements,但采用了不同的算法。planid以类似的方式计算。如果两个计划看起来很像,只有一些常量值或波动值(如成本或测量时间)存在差异,则认为它们相同。

对于 PostgreSQL 14 或更高版本,您可以通过使用queryid连接pg_stat_statements,找到pg_store_plans中条目的相应查询,如下所示。

SELECT s.query, p.plan FROM pg_store_plans p JOIN pg_stat_statements s USING (queryid);

计划 ID 的计算排除了计划的波动属性。另一方面,pg_store_plans视图中的plan不断显示这些波动属性的最新值。

pg_store_planspg_stat_statements各自单独维护其信息,因此存在某些不可避免的情况,尤其是对于执行频率低的条目,找不到相应的条目。

pg_store_plans_info 视图

pg_store_plans插件本身的统计信息,通过名为pg_store_plans_info的视图进行跟踪和提供。此视图仅包含一行。视图中的列如表 2 所示。

表 2. pg_store_plans_info

名称 类型 引用 描述
dealloc bigint 由于观察到比pg_store_plans.max更多的不同语句,关于最少执行语句的pg_store_plans条目被删除的总次数。
stats_reset timestamp with time zone 上次重置pg_store_plans视图的所有统计信息的时间。

函数

  • pg_store_plans_reset() returns void

    pg_store_plans_reset丢弃pg_store_plans到目前为止收集的所有统计信息。默认情况下,只有超级用户才能执行此函数。

  • pg_store_plans(showtext boolean) returns setof record

    pg_store_plans视图是根据一个名为pg_store_plans的函数定义的。

  • pg_store_plans_info() returns record

    pg_store_plans_info视图是根据一个函数定义的,该函数也叫pg_store_plans_info

  • pg_store_hash_query(query text) returns oid

    此函数计算查询文本的哈希值。pg_store_plans中的queryid使用相同的算法进行计算,因此该函数可用于与pg_store_plans进行连接。

  • pg_store_plans_textplan(query text) returns text

    此函数从pg_store_plansplan的原始表示形式生成一个普通的文本表示形式,当pg_store_plans.plan_formats = ‘raw’ 时显示在那里。由于结果计划文本是从 json 表示形式生成的,因此它可能与直接从 “EXPLAIN” 命令获取的内容略有不同。

  • pg_store_plans_jsonplan(query text) returns text

    此函数将 “简短格式 json 计划” 或 “原始格式” 转换为正常的 json 格式。简短格式 json 是pg_store_plansplan的内部格式,当pg_store_plans.plan_formats = ‘raw’ 时会显示在那里。

  • pg_store_plans_xmlplan(query text) returns text

    此函数从pg_store_plansplan的原始表示形式生成 XML 表示形式,当pg_store_plans.plan_formats = ‘raw’ 时显示在那里。

  • pg_store_plans_yamlplan(query text) returns text

    此函数从pg_store_plansplan的原始表示形式生成 YAML 表示形式,当pg_store_plans.plan_formats = ‘raw’ 时显示在那里。

配置参数

  • pg_store_plans.max (integer)

    pg_store_plans.max是该插件跟踪的最大计划数(即视图中的最大行数)。如果观察到比这更多的不同计划,则会丢弃有关执行最少的计划的信息。默认值为 1000。此参数只能在服务器启动时设置。

  • pg_store_plans.track (enum)

    pg_stat_statements类似,pg_store_plans.track控制插件对哪些语句进行计数。指定top跟踪顶层语句(由客户端直接发出的语句),指定all还会跟踪嵌套语句(例如,在函数中调用的语句,某些命令除外,请参阅下文),或指定none禁用语句统计信息收集。默认值为top。指定all时,仍会忽略CREATE EXTENSIONALTER EXTENSION下执行的命令。指定verbose以跟踪所有命令,包括all排除之外的命令。只有超级用户才能更改此设置。

    pg_store_plans.max_plan_length是要存储的原始(简短的 JSON)格式的计划的最大字节长度。如果计划文本的长度超过该值,则会在上限处截断计划文本。默认值为 5000。此参数只能在服务器启动时设置。

  • pg_store_plans.plan_storage (integer)

    pg_store_plans.plan_storage指定在服务器运行时如何存储计划文本。如果设置为file,则计划文本将像 pg_stat_statements 一样存储在临时文件中。设置为shmem,用于在内存中存储计划文本。默认值为 “file”。有关详细信息,请参阅下面的讨论

  • pg_store_plans.plan_format (enum)

    pg_store_plans.plan_format控制pg_store_plansplans的格式。text是默认值,以普通文本表示形式显示,也可以设为jsonxmlyaml,以相应的格式显示。设为raw用于获取可以传递给pg_store_plans_*plan函数的内部表示形式。

  • pg_store_plans.min_duration (integer)

    pg_store_plans.min_duration是触发记录语句计划的最小语句执行时间(以毫秒为单位)。将此值设置为零(默认值)会记录所有计划。只有超级用户才能更改此设置。

  • pg_store_plans.log_analyze (boolean)

    pg_store_plans.log_analyze会将EXPLAIN ANALYZE的输出(而不仅仅是EXPLAIN输出)记录在plan中。此参数默认为 off,处于关闭状态。

  • pg_store_plans.log_buffers (boolean)

    pg_store_plans.log_buffers会将EXPLAIN (ANALYZE, BUFFERS)的输出(而不仅仅是EXPLAIN输出)记录在plan中。此参数默认为 off,处于关闭状态。

  • pg_store_plans.log_timing (boolean)

    设置pg_store_plans.log_timing为 false 将不会记录实际耗时。在某些系统上,重复读取系统时钟带来的开销,会显著减慢查询速度,因此,对于每个执行节点,在只需要实际行数而不是确切的执行时间时,将此参数设置为 FALSE 可能很有用。在pg_store_plans.log_analyze设置为 TRUE 时,始终会测量整个语句的运行时间。默认值为 TRUE。

  • pg_store_plans.log_triggers (boolean)

    pg_store_plans.log_triggers会将触发器执行统计信息包含在记录的计划中。除非打开pg_store_plans.log_analyze,否则此参数不起作用。

  • pg_store_plans.verbose (boolean)

    pg_store_plans.verbose会将EXPLAIN VERBOSE的输出(而不仅仅是EXPLAIN输出)记录在plan中。此参数默认为 off,处于关闭状态。

  • pg_store_plans.save (boolean)

    pg_store_plans.save指定是否在服务器关闭时保存计划统计信息。如果是off,则不会在关机时保存统计信息,也不会在服务器启动时重新加载。默认值为on。此参数只能在postgresql.conf文件或数据库服务启动命令行中设置。

关于 plan_storage 设置的讨论

pg_store_plans会申请与pg_store_plans.max成正比的额外的共享内存。当设置pg_store_plans.plan_storage为 “shmem” 时,它会进一步申请额外的共享内存来存储计划文本,其数量为要存储的最大计划数(pg_store_plans.max)和单个计划的最大长度(pg_store_plans.max_plan_length)的乘积。如果设置pg_store_plans.plan_storage为 “file”,则计划文本将按原样写入临时文件。如果pg_store_plans.max不够大,无法存储所有计划,则会通过换出部分条目来回收新计划的空间。经过几轮换出后,pg_store_plans对临时文件运行垃圾回收,这对某些业务负载来说可能会产生一些影响。您可以在pg_store_plans_info.dealloc看到换出的频率有多高。

如果pg_store_plans.max足够大,不会发生垃圾回收,则建议将pg_store_plans.plan_storage设为 “file”。

这些参数必须在postgresql.conf中设置。示例设置如下:

# postgresql.conf
shared_preload_libraries = 'pg_store_plans, pg_stat_statements'
pg_store_plans.max = 10000
pg_store_plans.track = all

样例输出

让我们按如下方式,在postgresql.conf中配置一些扩展的参数:

# postgresql.conf
shared_preload_libraries = 'pg_store_plans,pg_stat_statements'
pg_store_plans.log_analyze = true
pg_store_plans.log_timing = false

连接 pg_stat_statements 和 pg_store_plans 视图,可以为我们提供更全面的信息,帮助提高 PostgreSQL 的性能。首先,我们需要确保重置两个视图中的信息,以丢弃这两个视图到目前为止收集的所有统计信息:

SELECT pg_store_plans_reset();
SELECT pg_stat_statements_reset();

然后你可以运行 pgbench 命令:

$ pgbench -i bench
$ pgbench -c10 -t1000 bench

然后,您可以使用以下查询来连接这两个视图:

bench=# \x
bench=# SELECT s.query, p.plan,
        p.calls as "plan calls", s.calls as "stmt calls",
        p.total_time / p.calls as "time/call", p.first_call, p.last_call
        FROM pg_stat_statements s
        JOIN pg_store_plans p USING (queryid) WHERE p.calls < s.calls
        ORDER BY query ASC, "time/call" DESC;

它会输出以下内容:

-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query      | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
plan       | Update on pgbench_tellers  (cost=0.00..7.88 rows=0 width=0)                                                                                                                                                                                                            +
           |   ->  Seq Scan on pgbench_tellers  (cost=0.00..7.88 rows=1 width=10)                                                                                                                                                                                                   +
           |         Filter: (tid = 1)
plan calls | 396
stmt calls | 10000
time/call  | 16.15434492676767
first_call | 2021-11-25 15:11:38.258838+09
last_call  | 2021-11-25 15:11:40.170291+09
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query      | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
plan       | Update on pgbench_tellers  (cost=0.14..8.15 rows=0 width=0)                                                                                                                                                                                                            +
           |   ->  Index Scan using pgbench_tellers_pkey on pgbench_tellers  (cost=0.14..8.15 rows=1 width=10)                                                                                                                                                                      +
           |         Index Cond: (tid = 8)                                                                                                                                                                                                                                          +
plan calls | 9604
stmt calls | 10000
time/call  | 10.287281695439345
first_call | 2021-11-25 15:11:40.161556+09
last_call  | 2021-11-25 15:12:09.957773+09
-[ RECORD 3 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query      | select s.query, p.plan, p.calls as "plan calls", s.calls as "stmt calls", p.total_time / p.calls as "time/call", p.first_call, p.last_call from pg_stat_statements s join pg_store_plans p using (queryid) where p.calls < s.calls order by query asc, "time/call" desc
plan       | Sort  (cost=309.71..313.88 rows=1667 width=104)                                                                                                                                                                                                                        +
           |   Sort Key: pg_stat_statements.query, ((pg_store_plans.total_time / (pg_store_plans.calls)::double precision)) DESC                                                                                                                                                    +
           |   ->  Merge Join  (cost=119.66..220.50 rows=1667 width=104)                                                                                                                                                                                                            +
           |         Merge Cond: (pg_stat_statements.queryid = pg_store_plans.queryid)                                                                                                                                                                                              +
           |         Join Filter: (pg_store_plans.calls < pg_stat_statements.calls)                                                                                                                                                                                                 +
           |         ->  Sort  (cost=59.83..62.33 rows=1000 width=48)                                                                                                                                                                                                               +
           |               Sort Key: pg_stat_statements.queryid                                                                                                                                                                                                                     +
           |               ->  Function Scan on pg_stat_statements  (cost=0.00..10.00 rows=1000 width=48)                                                                                                                                                                           +
           |         ->  Sort  (cost=59.83..62.33 rows=1000 width=72)                                                                                                                                                                                                               +
           |               Sort Key: pg_store_plans.queryid                                                                                                                                                                                                                         +
           |               ->  Function Scan on pg_store_plans  (cost=0.00..10.00 rows=1000 width=72)                                                                                                                                                                               +
plan calls | 3
stmt calls | 4
time/call  | 16.387161
first_call | 2021-11-25 15:20:57.978082+09
last_call  | 2021-11-25 15:23:48.631993+09
-[ RECORD 4 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query      | select s.query, p.plan, p.calls as "plan calls", s.calls as "stmt calls", p.total_time / p.calls as "time/call", p.first_call, p.last_call from pg_stat_statements s join pg_store_plans p using (queryid) where p.calls < s.calls order by query asc, "time/call" desc
plan       | Sort  (cost=309.71..313.88 rows=1667 width=104)                                                                                                                                                                                                                        +
           |   Sort Key: pg_stat_statements.query, ((pg_store_plans.total_time / (pg_store_plans.calls)::double precision)) DESC                                                                                                                                                    +
           |   Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                                                                 +
           |   ->  Merge Join  (cost=119.66..220.50 rows=1667 width=104)                                                                                                                                                                                                            +
           |         Merge Cond: (pg_stat_statements.queryid = pg_store_plans.queryid)                                                                                                                                                                                              +
           |         Join Filter: (pg_store_plans.calls < pg_stat_statements.calls)                                                                                                                                                                                                 +
           |         Rows Removed by Join Filter: 7                                                                                                                                                                                                                                 +
           |         ->  Sort  (cost=59.83..62.33 rows=1000 width=48)                                                                                                                                                                                                               +
           |               Sort Key: pg_stat_statements.queryid                                                                                                                                                                                                                     +
           |               Sort Method: quicksort  Memory: 27kB                                                                                                                                                                                                                     +
           |               ->  Function Scan on pg_stat_statements  (cost=0.00..10.00 rows=1000 width=48)                                                                                                                                                                           +
           |         ->  Sort  (cost=59.83..62.33 rows=1000 width=72)                                                                                                                                                                                                               +
           |               Sort Key: pg_store_plans.queryid                                                                                                                                                                                                                         +
           |               Sort Method: quicksort  Memory: 30kB                                                                                                                                                                                                                     +
           |               ->  Function Scan on pg_store_plans  (cost=0.00..10.00 rows=1000 width=72)                                                                                                                                                                               +
plan calls | 1
stmt calls | 4
time/call  | 4.46928
first_call | 2021-11-25 15:12:27.142535+09
last_call  | 2021-11-25 15:12:27.142536+09