F.29. pg_stat_statements

• F.29.1. pg_stat_statements 视图

• F.29.2. Functions

• F.29.3. 配置参数

• F.29.4. samples 输出

• F.29.5. Authors

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

必须通过将pg_stat_statements添加到postgresql.conf中的shared_preload_libraries来加载模块，因为它需要附加的共享内存。这意味着需要重新启动服务器才能添加或删除模块。

加载pg_stat_statements后，它将跟踪服务器所有数据库中的统计信息。要访问和处理这些统计信息，该模块提供一个视图pg_stat_statements以及 Util 功能pg_stat_statements_reset和pg_stat_statements。这些不是全局可用的，但可以使用CREATE EXTENSION pg_stat_statements为特定数据库启用。

F.29.1. pg_stat_statements 视图

该模块收集的统计信息可通过名为pg_stat_statements的视图使用。对于每个不同的数据库 ID，用户 ID 和查询 ID(该模块可以跟踪的最大不同语句数)，此视图包含一行。视图的列显示在Table F.21中。

表 F.21.pg_stat_statements列

Name	Type	References	Description
userid	oid	pg_authid.oid	执行该语句的用户的 OID
dbid	oid	pg_database.oid	执行该语句的数据库的 OID
queryid	bigint		内部哈希码，根据语句的解析树计算
query	text		代表声明文本
calls	bigint		执行次数
total_time	double precision		语句中花费的总时间(以毫秒为单位)
min_time	double precision		语句中花费的最短时间(以毫秒为单位)
max_time	double precision		语句中花费的最长时间(以毫秒为单位)
mean_time	double precision		语句中花费的平均时间(以毫秒为单位)
stddev_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，则为零)

出于安全原因，仅超级用户和pg_read_all_stats角色的成员被允许查看 SQL 文本和其他用户执行的查询的queryid。但是，如果视图已安装在他们的数据库中，则其他用户可以查看统计信息。

只要有计划的查询(即SELECT，INSERT，UPDATE和DELETE)根据内部哈希计算具有相同的查询结构，它们就会组合成一个pg_stat_statements条目。通常，如果两个查询在语义上等效，则两个查询在此意义上是相同的，只是出现在查询中的 Literals 常量的值除外。但是，将严格根据 Util 命令(即所有其他命令)的文本查询字符串进行比较。

当为了将查询与其他查询匹配而忽略了常量的值时，该常量将被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 使用隐藏的参数符号将函数局部变量的值插入查询中，因此SELECT i + 1 INTO j之类的 PL/pgSQL 语句将具有SELECT i + $2之类的代表性文本。

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

F.29.2. Functions

• pg_stat_statements_reset() returns void

  • pg_stat_statements_reset丢弃到目前为止pg_stat_statements收集的所有统计信息。默认情况下，此功能只能由超级用户执行。

• pg_stat_statements(showtext boolean) returns setof record

  • pg_stat_statements视图是根据也称为pg_stat_statements的函数定义的。Client 端可以直接调用pg_stat_statements函数，并且可以通过指定showtext := false来省略查询文本(即，与视图的query列相对应的OUT参数将返回 null)。此功能旨在支持可能希望避免重复检索长度不确定的查询文本的开销的外部工具。相反，此类工具可以缓存对每个条目本身观察到的第一个查询文本，因为pg_stat_statements本身就是这样做的，然后仅根据需要检索查询文本。由于服务器将查询文本存储在文件中，因此此方法可以减少物理 I/O，以便重复检查pg_stat_statements数据。

F.29.3. 配置参数

• pg_stat_statements.max ( integer )

  • pg_stat_statements.max是模块跟踪的最大语句数(即pg_stat_statements视图中的最大行数)。如果观察到的语句不同，则将丢弃有关执行最少的语句的信息。默认值为 5000.只能在服务器启动时设置此参数。

• pg_stat_statements.track ( enum )

  • pg_stat_statements.track控制模块将计算哪些语句。指定top可跟踪顶级语句(由 Client 端直接发出的语句)，all也可跟踪嵌套语句(例如在函数内调用的语句)，或none可禁用语句统计信息收集。默认值为top。只有超级用户可以更改此设置。

• pg_stat_statements.track_utility ( boolean )

  • pg_stat_statements.track_utility控制模块是否跟踪 Util 命令。Util 命令是SELECT，INSERT，UPDATE和DELETE以外的所有命令。默认值为on。只有超级用户可以更改此设置。

• 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'

pg_stat_statements.max = 10000
pg_stat_statements.track = all

F.29.4. samples 输出

bench=# SELECT pg_stat_statements_reset();

$ pgbench -i bench
$ pgbench -c10 -t300 bench

bench=# \x
bench=# SELECT query, calls, total_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_time DESC LIMIT 5;
-[ RECORD 1 ]---------------------------------------------------------------------
query       | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
calls       | 3000
total_time  | 9609.00100000002
rows        | 2836
hit_percent | 99.9778970000200936
-[ RECORD 2 ]---------------------------------------------------------------------
query       | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;
calls       | 3000
total_time  | 8015.156
rows        | 2990
hit_percent | 99.9731126579631345
-[ RECORD 3 ]---------------------------------------------------------------------
query       | copy pgbench_accounts from stdin
calls       | 1
total_time  | 310.624
rows        | 100000
hit_percent | 0.30395136778115501520
-[ RECORD 4 ]---------------------------------------------------------------------
query       | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2;
calls       | 3000
total_time  | 271.741999999997
rows        | 3000
hit_percent | 93.7968855088209426
-[ RECORD 5 ]---------------------------------------------------------------------
query       | alter table pgbench_accounts add primary key (aid)
calls       | 1
total_time  | 81.42
rows        | 0
hit_percent | 34.4947735191637631

F.29.5. Authors

板垣孝宏<[email protected]>。 Peter Geoghegan <[email protected]>添加的查询规范化。