二月 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_plans和pg_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 voidpg_store_plans_reset丢弃pg_store_plans到目前为止收集的所有统计信息。默认情况下，只有超级用户才能执行此函数。 -
pg_store_plans(showtext boolean) returns setof recordpg_store_plans视图是根据一个名为pg_store_plans的函数定义的。 -
pg_store_plans_info() returns recordpg_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_plans中plan的原始表示形式生成一个普通的文本表示形式，当pg_store_plans.plan_formats= ‘raw’ 时显示在那里。由于结果计划文本是从 json 表示形式生成的，因此它可能与直接从 “EXPLAIN” 命令获取的内容略有不同。 -
pg_store_plans_jsonplan(query text) returns text此函数将 “简短格式 json 计划” 或 “原始格式” 转换为正常的 json 格式。简短格式 json 是
pg_store_plans中plan的内部格式，当pg_store_plans.plan_formats= ‘raw’ 时会显示在那里。 -
pg_store_plans_xmlplan(query text) returns text此函数从
pg_store_plans中plan的原始表示形式生成 XML 表示形式，当pg_store_plans.plan_formats= ‘raw’ 时显示在那里。 -
pg_store_plans_yamlplan(query text) returns text此函数从
pg_store_plans中plan的原始表示形式生成 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 EXTENSION和ALTER 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_plans中plans的格式。text是默认值，以普通文本表示形式显示，也可以设为json、xml和yaml，以相应的格式显示。设为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