F.30. pg_stat_statements

F.30.1. pg_stat_statements视图
F.30.2. The pg_stat_statements_info View
F.30.3. 函数
F.30.4. 配置参数
F.30.5. 示例输出
F.30.6. 作者

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

该模块必须通过在postgresql.confshared_preload_libraries中增加pg_stat_statements来载入,因为它需要额外的共享内存。这意味着增加或移除该模块需要一次服务器重启。此外,必须启用查询ID计算器才能使模块处于active状态。如果compute_query_id参数设置为auto 或者 on,或者如果加载了计算查询 id 的第三方模块, 则会自动完成此操作。

pg_stat_statements激活时,它会跟踪该服务器 的所有数据库的统计信息。该模块提供了一个视图 pg_stat_statements以及函数pg_stat_statements_resetpg_stat_statements用于访问和操纵这些统计信息。这些视图 和函数不是全局可用的,但是可以用CREATE EXTENSION pg_stat_statements 为特定数据库启用它们。

F.30.1. pg_stat_statements视图

由该模块收集的统计信息可以通过一个名为 pg_stat_statements的视图使用。这个视图的每一行都包含 一个单独的数据库 ID、用户 ID 和查询 ID 以及它是否是顶级语句(最多到该模块可以追踪的可区分语句的数量)的组合。该视图的列如 表 F.21中所示。

表 F.21. pg_stat_statements

列类型

描述

userid oid (references pg_authid.oid)

执行该语句的用户的 OID

dbid oid (references pg_database.oid)

在其中执行该语句的数据库的 OID

toplevel bool

如果查询作为顶级 SQL 语句执行则为True(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,否则为零)

wal_records bigint

语句生成的 WAL 记录总数

wal_fpi bigint

语句生成的 WAL 整页图像总数

wal_bytes numeric

SQL 语句生成的 WAL 总量(以字节为单位)


由于安全性原因,只有超级用户和pg_read_all_stats 角色的成员被允许看到其他用户执行的查询 的 SQL 文本或者queryid。 不过,如果该视图被安装在其他用户的数据库中,那么他们就能够看见统 计信息。

只要可规划的查询(即SELECTINSERTUPDATE以及DELETE)根据一种内部哈希计算具有相同的查询结构,它们就会被组合到一个单一的pg_stat_statements项。通常,对于这里的目的,如果两个查询除了查询中的文本常量值之外在语义上等效,它们将会被认为是相同的。不过,功能性命令(即所有其他命令)会严格地以它们的文本查询字符串为基础进行比较。

注意

以下有关常量替换和queryid的详细信息仅适用于compute_query_id有效的情况。 queryid如果您使用外部模块来计算,您应该参考该外部模块的文档以获取详细信息。

当为了把一个查询与其他查询匹配,常数值会被忽略, 在pg_stat_statements显示中它会被一个参数符号, 比如$1所替换。查询文本的剩余部分就是具有与该pg_stat_statements项相关的特定queryid哈希值的第一个查询的文本。

在某些情况中,具有明显不同文本的查询可能会被融合到一个单一的pg_stat_statements项。通常这只会发生在语义等价的查询身上,但是也有很小的机会因为哈希碰撞的原因导致无关的查询被融合到一个项中(不过,对于属于不同用户或数据库的查询来说不会发生这种情况)。

由于queryid哈希值是根据查询被解析和分析后的表达计算的,对立的情况也可能存在:如果具有相同文本的查询由于参数(如不同的search_path设置)的原因而具有不同的含义,它们就可能作为不同的项存在。

pg_stat_statements的使用者可能希望使用 queryid(也许会与dbiduserid组合)作为一个项比查询文本更稳定和可靠的标识符。但是,有一点很重要的是,对于queryid哈希值稳定性只有有限的保障。因为该标识符是从解析分析后的树得来的,它的值是以这种形式出现的内部对象标识符的函数。这有一些违背直觉的含义。例如,如果有两个查询引用了同一个表,但是该表在两次查询之间被删除并且重建,显然这两个查询是完全一致的,但是pg_stat_statements将把它们认为是不同的。哈希处理也对机器架构以及平台的其他方面的差别很敏感。更进一步,认为PostgreSQL的不同主版本之间queryid将会保持稳定是不安全的。

根据经验,只有在底层服务器版本以及目录元数据细节保持完全相同时,queryid值才能被假定为稳定并且可比。两台参与到基于物理 WAL 重放的复制中的服务器会对相同的查询给出一样的queryid值。但是,逻辑复制模式并不保证在所有相关细节上都保持完全一样的复制,因此在逻辑复制机之间计算代价时,queryid并非是一个有用的标识符。如果有疑问,推荐直接进行测试。

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

有代表性的查询文本被保存在一个外部磁盘文件中,并且不会消耗共享内存。 因此,即便是很长的查询文本也能被成功的存储下来。不过,如果累积了很多 长的查询文本,该外部文件也会增长到很大。作为一种恢复方法,如果这样的 情况发生,pg_stat_statements可能会选择丢弃这些查询文本, 于是pg_stat_statements视图中的所有现有项将会显示空的 query域,不过与每个queryid相关联的 统计信息会被保留下来。如果发生这种情况,可以考虑减小 pg_stat_statements.max来防止复发。

planscalls并不总是匹配的, 因为计划和执行统计信息在它们各自的结束阶段更新,并且仅适用于成功的操作。 例如,如果一条语句计划成功但在执行阶段失败,则只会更新其计划统计信息。 如果因为使用了缓存计划而跳过计划,则只会更新其执行统计信息。

F.30.2. The pg_stat_statements_info View

通过视图pg_stat_statements_info来生成和记录pg_stat_statements 本身模块的统计信息。这个视图只有一行数据,视图列显示在 表 F.22中。

表 F.22. pg_stat_statements_info Columns

Column Type

Description

dealloc bigint

由于观察到比pg_stat_statements.max更多的不同语句,关于最少执行语句的pg_stat_statements条目被删除的总次数。

stats_reset timestamp with time zone

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


F.30.3. 函数

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

pg_stat_statements_reset丢弃到目前为止与指定的useriddbidqueryid相对应的pg_stat_statements收集的统计信息。 如果有任何参数未被指定,那么将对这些参数使用默认值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.30.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控制该模块是否会跟踪工具命令。工具命令是除了SELECTINSERTUPDATEDELETE之外所有的其他命令。默认值是on。 只有超级用户能够改变这个设置。

pg_stat_statements.track_planning (boolean)

pg_stat_statements.track_planning控制模块是否跟踪计划操作和持续时间。 启用此参数可能会导致明显的性能损失,尤其是当具有相同查询结构的 SQL 语句由许多竞争更新少数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.30.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.30.6. 作者

Takahiro Itagaki 。Peter Geoghegan 为它加入了查询正规化的功能。