PostgreSQL 教程

Appearance

F.32. pg_stat_statements — 跟踪 SQL 规划和执行的统计信息# ​

F.32.1. pg_stat_statements 视图
F.32.2. pg_stat_statements_info 视图
F.32.3. 函数
F.32.4. 配置参数
F.32.5. 样例输出
F.32.6. 作者

pg_stat_statements模块提供了一种方法来跟踪服务器执行的所有 SQL 语句的规划和执行统计信息。

必须通过将pg_stat_statements添加到shared_preload_libraries中来加载模块，因为这需要额外的共享内存。这意味着需要重新启动服务器才能添加或移除模块。此外，必须启用查询标识符计算才能使模块处于活动状态，如果将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.pg_stat_statements视图# ​

模块收集的统计信息通过名为pg_stat_statements的视图提供。此视图为数据库 ID、用户 ID、查询 ID 以及是否为顶级语句的每个不同组合包含一行（最多可跟踪不同语句的最大数量）。视图的列显示在表 F.22中。

表 F.22.pg_stat_statements列

列类型

描述

userid oid（引用 pg_authid.oid）

执行语句的用户的 OID

dbid oid（引用 pg_database.oid）

执行语句的数据库的 OID

toplevel bool

如果查询作为顶级语句执行，则为 True（如果 pg_stat_statements.track 设置为 top，则始终为 True）

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 双精度

执行语句所花费时间的总体标准差，以毫秒为单位

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 双精度

语句花费在读取数据文件块上的总时间，以毫秒为单位（如果启用了 track_io_timing，否则为零）

blk_write_time 双精度

语句花费在写入数据文件块上的总时间，以毫秒为单位（如果启用了 track_io_timing，否则为零）

temp_blk_read_time 双精度

语句花费在读取临时文件块上的总时间，以毫秒为单位（如果启用了 track_io_timing，否则为零）

temp_blk_write_time 双精度

语句花费在写入临时文件块上的总时间，以毫秒为单位（如果启用了 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

该语句发出代码所花费的总时间（以毫秒为单位）

出于安全原因，只有超级用户和拥有pg_read_all_stats角色权限的角色才能查看其他用户执行的查询的 SQL 文本和queryid。但是，如果视图已安装在其他用户的数据库中，其他用户可以看到统计信息。

可计划查询（即SELECT、INSERT、UPDATE、DELETE和MERGE）和实用程序命令被合并到一个pg_stat_statements条目中，只要它们根据内部哈希计算具有相同的查询结构。通常，如果两个查询在语义上等效，除了查询中出现的文字常量的值之外，它们将被视为相同。

注意

以下有关常量替换和queryid的详细信息仅在启用compute_query_id时适用。如果您使用外部模块来计算queryid，则应参阅其文档以获取详细信息。

当常量的值被忽略以将查询与其他查询匹配时，该常量将被参数符号替换，例如$1，在pg_stat_statements显示中。查询文本的其余部分是第一个具有与pg_stat_statements条目关联的特定queryid哈希值。查询。

可以在pg_stat_statements中观察到可以应用规范化的查询，特别是在条目释放率很高的情况下。为了降低发生这种情况的可能性，请考虑增加pg_stat_statements.max。在第 F.32.2 节中讨论的pg_stat_statements_info视图提供了有关条目释放的统计信息。

在某些情况下，文本明显不同的查询可能会合并到一个pg_stat_statements条目中。通常，这只会发生在语义等效的查询中，但哈希冲突导致不相关的查询合并到一个条目中的可能性很小。（然而，这不会发生在属于不同用户或数据库的查询中。）

由于queryid哈希值是在查询的后解析分析表示上计算的，因此反过来也是可能的：具有相同文本的查询可能会显示为单独的条目，如果它们由于search_path设置不同等因素而具有不同的含义。

pg_stat_statements的使用者可能希望使用queryid（可能与dbid和userid结合使用）作为比查询文本更稳定、更可靠的每个条目的标识符。但是，重要的是要了解queryid哈希值的稳定性只有有限的保证。由于标识符是从后解析分析树派生的，因此它的值是其他因素（包括此表示中出现的内部对象标识符）的函数。这有一些违反直觉的含义。例如，如果pg_stat_statements在两次查询执行之间引用了一个被删除并重新创建的表，则它会将两个明显相同的查询视为不同的查询。哈希过程也对机器架构和平台的其他方面差异敏感。此外，不能安全地假设queryid在PostgreSQL的主要版本中是稳定的。

根据经验，只有在底层服务器版本和目录元数据详细信息完全相同的情况下，才能假设queryid值是稳定且可比较的。参与基于物理 WAL 重放的复制的两个服务器可以预期对相同的查询具有相同的queryid值。但是，逻辑复制方案并不承诺在所有相关详细信息中保持副本相同，因此queryid不会成为跨一组逻辑副本累积成本的有用标识符。如有疑问，建议直接测试。

用于替换代表性查询文本中常量的参数符号从原始查询文本中最高$*n*参数之后的下一个数字开始，如果没有，则从$1开始。值得注意的是，在某些情况下可能存在影响此编号的隐藏参数符号。例如，PL/pgSQL使用隐藏的参数符号将函数局部变量的值插入查询中，因此类似SELECT i + 1 INTO j的PL/pgSQL语句将具有类似SELECT i + $2的代表性文本。

代表性查询文本保存在外部磁盘文件中，不占用共享内存。因此，即使非常长的查询文本也可以成功存储。但是，如果累积了许多长查询文本，外部文件可能会变得难以管理地大。如果发生这种情况，作为恢复方法，pg_stat_statements可能会选择丢弃查询文本，此时pg_stat_statements视图中的所有现有条目都将显示空query字段，尽管与每个queryid关联的统计信息都将保留。如果发生这种情况，请考虑减少pg_stat_statements.max以防止再次发生。

plans和calls并不总是匹配，因为规划和执行统计信息在它们各自的结束阶段更新，并且仅对成功操作更新。例如，如果语句规划成功但在执行阶段失败，则仅更新其规划统计信息。如果跳过规划是因为使用了缓存计划，则仅更新其执行统计信息。

F.32.2.pg_stat_statements_info视图# ​

pg_stat_statements模块本身的统计信息被跟踪并通过名为pg_stat_statements_info的视图提供。此视图仅包含一行。视图的列显示在表 F.23中。

表 F.23.pg_stat_statements_info列

列类型

描述

dealloc bigint

关于最少执行语句的 pg_stat_statements 条目被释放的总次数，因为观察到的不同语句多于 pg_stat_statements.max

stats_reset timestamp with time zone

pg_stat_statements 视图中所有统计信息上次重置的时间。

F.32.3. 函数# ​

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 参数将返回 null）。此功能旨在支持可能希望避免重复检索长度不定的查询文本的外部工具。此类工具可以自己缓存为每个条目观察到的第一个查询文本，因为这是 pg_stat_statements 本身所做的全部工作，然后仅在需要时检索查询文本。由于服务器将查询文本存储在一个文件中，因此这种方法可以减少对 pg_stat_statements 数据的重复检查的物理 I/O。

F.32.4. 配置参数# ​

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 = all

F.32.5. 示例输出# ​

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. 作者# ​

Takahiro Itagaki<[[email protected]](/cdn-cgi/l/email-protection#0d64796c6a6c666423796c666c65647f624d627e7e23637979236e6223677d)>。Peter Geoghegan<[[email protected]](/cdn-cgi/l/email-protection#8cfce9f8e9feccbee2e8fdf9ede8feede2f8a2efe3e1)>添加了查询规范化。