Postgresql 中文操作指南
F.32. pg_stat_statements — track statistics of SQL planning and execution #
pg_stat_statements 模块提供了一种机制，用于跟踪服务器执行的所有 SQL 语句的计划和执行统计信息。
必须通过向 postgresql.conf 中的 shared_preload_libraries 添加 pg_stat_statements 来加载模块，因为它需要额外的共享内存。这意味着添加或删除模块需要重新启动服务器。此外，必须启用查询标识符计算才能使模块处于活动状态，如果将 compute_query_id 设置为 auto 或 on，或者加载任何计算查询标识符的第三方模块，则会自动执行此操作。
当激活 pg_stat_statements 时，它追踪服务器所有数据库的统计信息。为了访问和操控这些统计信息，该模块提供了视图 pg_stat_statements 和 pg_stat_statements_info，以及实用函数 pg_stat_statements_reset 和 pg_stat_statements。它们无法全局使用，但可以使用 CREATE EXTENSION pg_stat_statements 为特定数据库启用它们。
F.32.1. The pg_stat_statements View #
模块收集的统计信息通过名为 pg_stat_statements 的视图提供。此视图为数据库 ID、用户 ID、查询 ID 及其是否为顶级语句（最多为模块可以跟踪的不同语句的最大数量）的每个不同组合包含一行。视图的列显示在 Table F.22 中。
Table F.22. pg_stat_statements Columns
Column Type Description |
userid oid （引用 pg_authid . oid ）执行声明的用户 OID |
dbid oid （引用 pg_database . oid ）执行声明的数据库 OID |
toplevel bool 如果查询作为顶级声明执行，则为真（如果将 pg_stat_statements.track 设置为 top ，则始终为真） |
queryid bigint 哈希代码，用于标识相同的规范化查询。 |
query text 代表性声明的文本 |
plans bigint 规划声明的次数（如果已启用 pg_stat_statements.track_planning ，否则为零） |
total_plan_time double precision 规划声明的总时间（以毫秒为单位）（如果已启用 pg_stat_statements.track_planning ，否则为零） |
min_plan_time double precision 规划声明所用的最少时间（以毫秒为单位）（如果已启用 pg_stat_statements.track_planning ，否则为零） |
max_plan_time double precision 计划语句花费的最长时间，以毫秒为单位（如果 pg_stat_statements.track_planning 已启用，否则为零） |
mean_plan_time double precision 计划语句的平均花费时间，以毫秒为单位（如果 pg_stat_statements.track_planning 已启用，否则为零） |
stddev_plan_time double precision 计划语句花费时间的总体标准差，以毫秒为单位（如果 pg_stat_statements.track_planning 已启用，否则为零） |
calls bigint 执行语句的次数 |
total_exec_time double precision 执行语句花费的总时间，以毫秒为单位 |
min_exec_time double precision 执行语句花费的最短时间，以毫秒为单位 |
max_exec_time double precision 执行语句花费的最长时间，以毫秒为单位 |
mean_exec_time double precision 执行语句的平均花费时间，以毫秒为单位 |
stddev_exec_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 启用，则非零，否则为零） |
wal_records bigint 该语句生成的 WAL 记录的总数 |
wal_fpi bigint 该语句生成的 WAL 整页图像的总数 |
wal_bytes numeric 该语句生成的 WAL 总量（以字节为单位） |
jit_functions bigint 该语句 JIT 编译过的函数总数 |
jit_generation_time double precision 该语句用于生成 JIT 代码的总时间（单位毫秒） |
jit_inlining_count bigint 已内联函数的次数 |
jit_inlining_time double precision 该语句用于内联函数的总时间（单位毫秒） |
jit_optimization_count bigint 语句已优化的次数 |
jit_optimization_time double precision 该语句用于优化的总时间（单位毫秒） |
jit_emission_count bigint 已发出代码的次数 |
jit_emission_time double precision 该语句用于发出代码的总时间（单位毫秒） |
出于安全考虑，只有超级用户和具有 @{5} 角色权限的角色才能查看其他用户执行的查询的 SQL 文本和 @{6}。但如果在自己的数据库中安装了视图，其他用户也可以查看统计信息。
可规划查询（即 @{7}、@{8}、@{9}、@{10} 和 @{11}）和实用程序命令会根据内部哈希计算合并到一个 @{12} 条目中，只要它们具有相同的查询结构。通常，如果具有相同的语义等价性（查询中出现文本常量的值除外），将考虑两个查询是否相同。
Note
以下有关常量替换和 queryid 的详细信息仅当 compute_query_id 启用时才适用。如果您使用外部模块来计算 queryid，则您应参阅其文档以获取详细信息。
当常量值被忽略以匹配查询与其他查询时，常量会被 @{13} 等参数符号替换，显示为 @{14}。查询文本的其余部分是第一个与该 @{16} 条目相关联的 @{15} 哈希值相匹配的查询部分。
可应用规范化的查询可能会在 @{17} 中观察到常量值，尤其是在条目释放率较高时。为了减少发生这种情况的可能性，请考虑增加 @{18}。在 @{20} 中讨论的 @{19} 视图提供了有关条目释放的统计信息。
在某些情况下，具有明显不同文本的查询可能会合并到单个 @{21} 条目中。通常，只会对具有相同语义等价性的查询执行此操作，但哈希冲突会导致不相关的查询合并到一个条目中的可能性很小。（然而，属于不同用户或数据库的查询不会出现这种情况。）
由于 @{22} 哈希值是在查询的后解析分析表示上计算的，因此反过来也可能：具有相同文本的查询可能会显示为单独的条目，如果它们的含义由于 @{23} 等不同因素而不同。
@{24} 的使用者可能希望将 @{25}（可能与 @{26} 和 @{27} 结合使用）用作比其查询文本更稳定、更可靠的每个条目的标识符。然而，了解围绕 @{28} 哈希值的稳定性只有有限的担保是很重要的。由于标识符源自后解析分析树，因此其值不仅取决于此表示中出现的内部对象标识符。这有一些违反直觉的含义。例如，如果在执行两次查询之间引用一个在两次执行之间被删除并重新创建的表，@{29} 会将两个看似相同的查询视作不同的查询。哈希过程还对机器架构和平台的其他方面中的差异敏感。此外，无法确保 @{30} 在 PostgreSQL 的主要版本之间是稳定的。
可以预期参与基于物理 WAL 重放的复制的两个服务器将为相同的查询拥有相同的 @{31} 值。但是，逻辑复制方案并不会承诺使副本在所有相关细节上保持相同，因此 @{32} 不会成为用于累积一系列逻辑副本的成本的有用标识符。如有疑问，建议进行直接测试。
通常，可以假设 @{33} 值在 PostgreSQL 的次要版本发布之间是稳定的，前提是实例在同一机器架构上运行，并且目录元数据详细信息匹配。次要版本之间的兼容性只会作为最后手段才能中断。
用于替换代表性查询文本中的常量的参数符号从原始查询文本中的 @{34} 参数后的下一个数字开始，如果没有则从 @{35} 开始。值得注意的是，在某些情况下可能存在影响此编号的隐藏参数符号。例如，PL/pgSQL 使用隐藏参数符号将函数局部变量的值插入到查询中，以便像 @{36} 这样的 PL/pgSQL 语句的代表文本为 @{37}。
代表性查询文本保存在外部磁盘文件中，并且不使用共享内存。因此，甚至非常长的查询文本也可以成功存储。然而，如果积累了许多长查询文本，外部文件可能会变得难以管理的大。如果发生这种情况，一种恢复方法是 @{38} 可以选择丢弃查询文本，此时 @{39} 视图中的所有现有条目将显示为 null @{40} 字段，尽管与每个 @{41} 关联的统计信息得以保留。如果出现这种情况，请考虑减少 @{42} 以防止复发。
并不要总期望 @{43} 和 @{44} 匹配，因为规划和执行统计信息是在它们各自的结束阶段更新的，并且仅针对成功操作更新。例如，如果一个语句已成功规划但在执行阶段失败，则只会更新其规划统计信息。如果由于使用了已缓存的计划而跳过规划，则只会更新其执行统计信息。
F.32.2. The pg_stat_statements_info View #
@{45} 模块本身的统计信息会被跟踪并通过名为 @{46} 的视图公开。此视图只包含一行。视图的列显示在 @{47} 中。
Table F.23. pg_stat_statements_info Columns
Column Type Description |
dealloc bigint 关于执行最少的语句的 pg_stat_statements 条目的总数，因为发生了多于 pg_stat_statements.max 个其他语句 |
stats_reset timestamp with time zone pg_stat_statements 视图中所有统计最近一次重置时间 |
F.32.3. Functions #
-
pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void
-
pg_stat_statements_reset 弃用 pg_stat_statements 收集到的与指定 userid、dbid 和 queryid 相对应的统计信息。如果未指定任何参数，则为每个参数使用默认值 0(无效)，并且将重置与其他参数匹配的统计信息。如果未指定任何参数或所有指定参数为 0(无效)，它将弃用所有统计信息。如果 pg_stat_statements 视图中的所有统计信息被弃用，它还将重置 pg_stat_statements_info 视图中的统计信息。默认情况下，此函数只能由超级用户执行。使用 GRANT 可授予他人访问权限。
-
-
pg_stat_statements(showtext boolean) returns setof record
-
pg_stat_statements 视图定义了一个也称为 pg_stat_statements 的函数。客户端完全有可能直接调用 pg_stat_statements 函数，通过指定 showtext := false 省略查询文本（即，与视图的 query 列对应的 OUT 参数将返回空值）。此功能旨在支持可能希望避免反复检索长度不定的查询文本的开销的外部工具。此类工具可以代为缓存它自己对每个条目观察到的第一个查询文本，因为这正是 pg_stat_statements 本身所做的，然后按需检索查询文本。由于服务器会将查询文本存储在一个文件中，因此这种方法可以减少重复检查 pg_stat_statements 数据的物理 I/O 操作。
-
F.32.4. Configuration Parameters #
-
pg_stat_statements.max (integer)
-
pg_stat_statements.max 是模块跟踪的最大语句数量（即 pg_stat_statements 视图中的最大行数）。如果观测到的不同语句比该数量多，将弃用有关执行最少的语句的信息。可在 pg_stat_statements_info 视图中查看此类信息被弃用的次数。默认值为 5000。此参数只能在服务器启动时设置。
-
-
pg_stat_statements.track (enum)
-
pg_stat_statements.track 控制模块统计哪些语句。指定 top 以跟踪顶级语句（客户端直接发出的语句），指定 all 还跟踪嵌套语句（例如在函数中调用的语句），或指定 none 以禁用语句统计信息的收集。默认值为 top。只有超级用户可以更改此设置。
-
-
pg_stat_statements.track_utility (boolean)
-
pg_stat_statements.track_utility 控制模块是否跟踪实用程序命令。实用程序命令是指 SELECT、INSERT、UPDATE、DELETE 和 MERGE 以外的所有命令。默认值为 on。只有超级用户可以更改此设置。
-
-
pg_stat_statements.track_planning (boolean)
-
pg_stat_statements.track_planning 控制模块是否跟踪计划操作和持续时间。启用此参数可能会带来明显的性能损失，尤其是当结构相同的语句由多个并行连接执行时，这些连接争相更新少量 pg_stat_statements 条目。默认值为 off。只有超级用户可以更改此设置。
-
-
pg_stat_statements.save (boolean)
-
pg_stat_statements.save 指定是否在服务器关机之间保存语句统计信息。如果是 off，则统计信息在关机时不会保存，也不会在服务器启动时重新加载。默认值为 on。此参数只能在 postgresql.conf 文件或服务器命令行中设置。
-
此模块需要与 pg_stat_statements.max 成正比的附加共享内存。请注意，只要加载模块，就会消耗此内存，即使 pg_stat_statements.track 设置为 none 也是如此。
这些参数必须在 postgresql.conf 中设置。典型用法可能是：
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on
pg_stat_statements.max = 10000
pg_stat_statements.track = allF.32.5. Sample Output #
bench=# SELECT pg_stat_statements_reset();
$ pgbench -i bench
$ pgbench -c10 -t300 bench
bench=# \x
bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+--------------------------------------------------------------------
query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
calls | 3000
total_exec_time | 25565.855387
rows | 3000
hit_percent | 100.0000000000000000
-[ RECORD 2 ]---+--------------------------------------------------------------------
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls | 3000
total_exec_time | 20756.669379
rows | 3000
hit_percent | 100.0000000000000000
-[ RECORD 3 ]---+--------------------------------------------------------------------
query | copy pgbench_accounts from stdin
calls | 1
total_exec_time | 291.865911
rows | 100000
hit_percent | 100.0000000000000000
-[ RECORD 4 ]---+--------------------------------------------------------------------
query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls | 3000
total_exec_time | 271.232977
rows | 3000
hit_percent | 98.8454011741682975
-[ RECORD 5 ]---+--------------------------------------------------------------------
query | alter table pgbench_accounts add primary key (aid)
calls | 1
total_exec_time | 160.588563
rows | 0
hit_percent | 100.0000000000000000
bench=# SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements AS s
WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2';
bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+--------------------------------------------------------------------
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls | 3000
total_exec_time | 20756.669379
rows | 3000
hit_percent | 100.0000000000000000
-[ RECORD 2 ]---+--------------------------------------------------------------------
query | copy pgbench_accounts from stdin
calls | 1
total_exec_time | 291.865911
rows | 100000
hit_percent | 100.0000000000000000
-[ RECORD 3 ]---+--------------------------------------------------------------------
query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls | 3000
total_exec_time | 271.232977
rows | 3000
hit_percent | 98.8454011741682975
-[ RECORD 4 ]---+--------------------------------------------------------------------
query | alter table pgbench_accounts add primary key (aid)
calls | 1
total_exec_time | 160.588563
rows | 0
hit_percent | 100.0000000000000000
-[ RECORD 5 ]---+--------------------------------------------------------------------
query | vacuum analyze pgbench_accounts
calls | 1
total_exec_time | 136.448116
rows | 0
hit_percent | 99.9201915403032721
bench=# SELECT pg_stat_statements_reset(0,0,0);
bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+-----------------------------------------------------------------------------
query | SELECT pg_stat_statements_reset(0,0,0)
calls | 1
total_exec_time | 0.189497
rows | 1
hit_percent |
-[ RECORD 2 ]---+-----------------------------------------------------------------------------
query | SELECT query, calls, total_exec_time, rows, $1 * shared_blks_hit / +
| nullif(shared_blks_hit + shared_blks_read, $2) AS hit_percent+
| FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT $3
calls | 0
total_exec_time | 0
rows | 0
hit_percent |F.32.6. Authors #
Peter Geoghegan < link:mailto:peter@2ndquadrant.com[peter@2ndquadrant.com]> 添加了 Takahiro Itagaki < link:mailto:itagaki.takahiro@oss.ntt.co.jp[itagaki.takahiro@oss.ntt.co.jp]> 的查询规范化。