Postgresql - pg_stat_statement

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

如果想要开启模块，必须在配置文件中将 pg_stat_statements 添加到 shared_preload_libraries中。因为它需要额外的共享内存，所以必须重启服务添加或删除。

当 pg_stat_statements 被夹杂，会跟踪服务器所有的数据库的统计信息。模块提供了视图以及一些function。这些并不会在所有数据库中应用。而是需要在数据库中使用 CREATE EXTENSION pg_stat_statements; 进行配置。

 

1. VIEW

模块收集的统计信息通过 pg_stat_statements 提供。

Name	Type	References	Description
userid	oid	pg_authid.oid	OID of user who executed the statement
dbid	oid	pg_database.oid	OID of database in which the statement was executed
queryid	bigint		Internal hash code, computed from the statement's parse tree
query	text		Text of a representative statement
calls	bigint		Number of times executed
total_time	double precision		Total time spent in the statement, in milliseconds
min_time	double precision		Minimum time spent in the statement, in milliseconds
max_time	double precision		Maximum time spent in the statement, in milliseconds
mean_time	double precision		Mean time spent in the statement, in milliseconds
stddev_time	double precision		Population standard deviation of time spent in the statement, in milliseconds
rows	bigint		Total number of rows retrieved or affected by the statement
shared_blks_hit	bigint		Total number of shared block cache hits by the statement
shared_blks_read	bigint		Total number of shared blocks read by the statement
shared_blks_dirtied	bigint		Total number of shared blocks dirtied by the statement
shared_blks_written	bigint		Total number of shared blocks written by the statement
local_blks_hit	bigint		Total number of local block cache hits by the statement
local_blks_read	bigint		Total number of local blocks read by the statement
local_blks_dirtied	bigint		Total number of local blocks dirtied by the statement
local_blks_written	bigint		Total number of local blocks written by the statement
temp_blks_read	bigint		Total number of temp blocks read by the statement
temp_blks_written	bigint		Total number of temp blocks written by the statement
blk_read_time	double precision		Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_time	double precision		Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

 

为了安全，只有superuser和 pg_read_all_stats role 用户可以访问 SQL text 和 queryid。其他用户可以访问 statistics。

只要根据内部哈希计算具有相同的查询结构，可计划查询（即SELECT，INSERT，UPDATE和DELETE）就会合并到单个pg_stat_statements条目中。 通常，如果两个查询在语义上等效，则除了查询中出现的文字常量的值之外，它们将被视为相同。 但是，实用命令（即所有其他命令）严格地根据其文本查询字符串进行比较。

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

在某些情况下，具有明显不同文本的查询可能会合并到单个pg_stat_statements条目中。 通常情况下，这只会发生在语义上等效的查询中，但是哈希冲突的可能性很小，导致无关的查询被合并到一个条目中。

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

 

pg_stat_statements的使用者可能希望使用queryid（可能与dbid和userid结合使用）作为每个条目比其查询文本更稳定和可靠的标识符。但是，重要的是要理解对queryid哈希值的稳定性只有有限的保证。由于标识符是从解析后分析树派生的，因此其值是该表示中出现的内部对象标识符的函数。这有一些违反直觉的含义。例如，如果pg_stat_statements引用在两个查询的执行之间删除并重新创建的表，则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视图中的所有现有条目都将显示空查询字段，但保留与每个queryid关联的统计信息。如果发生这种情况，请考虑减少pg_stat_statements.max以防止再次发生。

 

2. Functions

pg_stat_statements_reset() returns void

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

pg_stat_statements(showtext boolean) returns setof record

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

 

3. Configuration Parameters

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

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

pg_stat_statements.track_utility (boolean)：是否跟踪实用程序命令。 实用程序命令是SELECT，INSERT，UPDATE和DELETE以外的所有命令。 默认值为on。 只有超级用户才能更改此设置。

pg_stat_statements.save (boolean)：是否跨服务器关闭保存语句统计信息。 如果已关闭，则统计信息不会在关闭时保存，也不会在服务器启动时重新加载。 默认值为on。 此参数只能在postgresql.conf文件或服务器命令行中设置。