【译】postgresql 扩展工具 sql跟踪执行统计扩展 pg_stat_statements

15.1 介绍

原文地址：

https://www.postgresql.org/docs/current/pgstatstatements.html#id-1.11.7.39.8

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

pg_stat_statements是一个内置模块。

必须通过将pg_stat_statements添加到postgresql.conf中的shared_preload_libraries来加载模块，因为它需要其他共享内存。这意味着需要重启服务器来添加或删除模块。此外，必须启用查询标识符计算，以便将要激活的模块，如果Compute_Query_ID设置为自动或ON，则会自动完成，或者加载计算查询标识符的任何第三方模块。

当pg_stat_statements处于活动状态时，它会在服务器的所有数据库中跟踪统计信息。要访问和操作这些统计信息，模块提供了pg_stat_statements和pg_stat_statements_info和实用程序函数pg_stat_statements_reset和pg_stat_statements的视图。这些在全局中不可用，但可以为特定数据库启用，具有创建扩展名pg_stat_statements。

15.2 视图 pg_stat_statements

通过名为PG_STAT_STATEMENTS的视图模块提供收集的统计数据。此视图包含一行用于数据库ID，用户ID，查询ID的每个不同组合以及是否是它是一个顶级语句（最多一个模块可以跟踪的不同语句数）。视图的列如表F.21所示。

表F.21 pg_stat_statements列信息

出于安全原因，只允许仅允许PG_Read_All_Stats角色的超级用户和成员查看其他用户执行的查询的query text和queryid。但是，其他用户可以看到统计信息，如果查看已在其数据库中安装了视图。

每当根据内部哈希计算时它们具有相同的查询结构，可在单个pg_stat_statements条目中组合到单个pg_stat_statements条目中的可批准查询（即，查询，插入，更新和删除）。通常，如果它们是语义等同物，则两个查询将被认为是相同的，除了查询中出现的文字常量值。然而，实用程序命令（即所有其他命令）都是根据他们的文本查询字符串进行比较的。

注意
以下有关常量替换和查询的详细信息仅在启用Compute_Query_ID时应用。如果使用外部模块而不是计算QueryId，则应有关详细信息，请参阅其文档。

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

在某些情况下，具有明显不同文本的查询可能会合并到单个 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 使用隐藏参数符号将函数局部变量的值插入到查询中，因此 PL/pgSQL 语句如 SELECT i + 1 INTO j 将具有代表性文本，如 SELECT i + $2。

代表查询文本保存在外部磁盘文件中，不会消耗共享内存。因此，即使是非常冗长的查询文本也可以成功存储。但是，如果累积了许多长查询文本，则外部文件可能会增长无管理。作为恢复方法如果发生这种情况，pg_stat_statements可以选择丢弃查询文本，于是pg_stat_statements视图中的所有现有条目将显示为null查询字段，但保留与每个查询的统计信息。如果发生这种情况，请考虑减少pg_stat_statements.max以防止复发。

计划和呼叫并非总是匹配，因为规划和执行统计数据在各自的结束阶段更新，并且仅用于成功的操作。例如，如果在执行阶段在执行阶段成功计划但失败了语句，则只会更新其计划统计信息。如果跳过规划，因为使用了缓存的计划，则只有其执行统计信息将被更新。

15.3 视图 pg_stat_statements_info

跟踪pg_stat_statements模块本身的统计信息，并通过名为pg_stat_statements_info的视图提供可用的。此视图仅包含单行。视图的列如表F.22所示。

表F.22 pg_stat_statements_info列

15.4 函数

pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void
复制

pg_stat_statements_Reset丢弃到目前为止收集的统计信息由对应于指定的UserID，dbid和查询的pg_stat_statements收集。如果未指定任何参数，则将默认值0（invalid）用于它们中的每一个，并且将重置与其他参数匹配的统计信息。如果未指定参数或所有指定的参数为0（invalid），则它将丢弃所有统计信息。如果丢弃pg_stat_statements视图中的所有统计信息，则它还将重置pg_stat_statements_info视图中的统计信息。默认情况下，此函数只能由超级用户执行。可以使用授权授予其他人访问。

pg_stat_statements(showtext boolean) returns setof record
复制

pg_stat_statements视图是根据也名为pg_stat_statements的函数定义的。客户端可以直接调用pg_stat_statements函数，并通过指定showtext：= false省略了查询文本（即与视图的查询列对应的Out参数将返回NULL）。此功能旨在支持可能希望避免反复检索不确定长度的查询文本的开销的外部工具。此类工具可以改为缓存为每个条目本身观察到的第一个查询文本，因为这是所有pg_stat_statements本身，然后仅根据需要检索查询文本。由于服务器存储文件中的查询文本，因此该方法可以减少对PG_STAT_STATEMENTS数据的重复检查的物理I / O.

15.5 配置参数

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,UPDATEandDELETE。默认值为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，则不会在Shutdown时保存统计信息，也不会在服务器启动时重新加载。默认值为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
复制

15.6 示例输出

初始化测试数据

postgres=#  select pg_stat_statements_reset();                                   pg_stat_statements_reset 
--------------------------
 
(1 row)

postgres=# \q

[postgres@pg14 ~]$ pgbench -i -p 1931 postgres
dropping old tables...
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.14 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.38 s (drop tables 0.03 s, create tables 0.01 s, client-side generate 0.16 s, vacuum 0.11 s, primary keys 0.07 s).
[postgres@pg14 ~]$ pgbench  -c10 -t300 postgres -p 1931
pgbench (14.2)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 300
number of transactions actually processed: 3000/3000
latency average = 10.479 ms
initial connection time = 27.327 ms
tps = 954.290746 (without initial connection time)
复制

查询：

postgres=# \x
Expanded display is on.
postgres=# 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 | 9195.896443999985
rows            | 3000
hit_percent     | 99.9980330448465775
-[ RECORD 2 ]---+--------------------------------------------------------------------
query           | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls           | 3000
total_exec_time | 4949.097551999996
rows            | 3000
hit_percent     | 99.9944354793834511
-[ RECORD 3 ]---+--------------------------------------------------------------------
query           | copy pgbench_accounts from stdin
calls           | 1
total_exec_time | 149.162769
rows            | 100000
hit_percent     | 100.0000000000000000
-[ RECORD 4 ]---+--------------------------------------------------------------------
query           | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls           | 3000
total_exec_time | 119.58007300000034
rows            | 3000
hit_percent     | 98.7629329734592892
-[ RECORD 5 ]---+--------------------------------------------------------------------
query           | vacuum analyze pgbench_accounts
calls           | 1
total_exec_time | 59.459955
rows            | 0
hit_percent     | 99.9203028491731421

postgres=# 

postgres=#  SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements AS s
postgres-#             WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2';
-[ RECORD 1 ]------------+-
pg_stat_statements_reset | 

--相关UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2 的统计信息已没有

postgres=# 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 | 4949.097551999996
rows            | 3000
hit_percent     | 99.9944354793834511
-[ RECORD 2 ]---+--------------------------------------------------------------------
query           | copy pgbench_accounts from stdin
calls           | 1
total_exec_time | 149.162769
rows            | 100000
hit_percent     | 100.0000000000000000
-[ RECORD 3 ]---+--------------------------------------------------------------------
query           | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls           | 3000
total_exec_time | 119.58007300000034
rows            | 3000
hit_percent     | 98.7629329734592892
-[ RECORD 4 ]---+--------------------------------------------------------------------
query           | vacuum analyze pgbench_accounts
calls           | 1
total_exec_time | 59.459955
rows            | 0
hit_percent     | 99.9203028491731421
-[ RECORD 5 ]---+--------------------------------------------------------------------
query           | alter table pgbench_accounts add primary key (aid)
calls           | 1
total_exec_time | 55.450209
rows            | 0
hit_percent     | 100.0000000000000000

postgres=# 

-- 清除所有统计信息
postgres=# SELECT pg_stat_statements_reset(0,0,0);
-[ RECORD 1 ]------------+-
pg_stat_statements_reset | 

postgres=# 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.070974
rows            | 1
hit_percent     | 

postgres=# 

复制

15.7 作者

takahiro itagaki itagaki.takahiro@oss.ntt.co.jp。彼得地理草Peter@2ndquadrant.com添加了查询标准化。