pg_stat_statements

pg_stat_statements

pg_stat_statements 提供了一种方法，用于跟踪服务器执行的所有 SQL 语句的计划和执行统计信息。这些统计信息对于识别性能瓶颈、分析查询模式以及优化数据库非常有用。

安装扩展

-- 需要在数据库中创建扩展
CREATE EXTENSION pg_stat_statements;

配置参数

需要在 postgresql.conf 中配置以下参数（或通过 ALTER SYSTEM 设置）：

# 1. 将模块添加到 shared_preload_libraries
shared_preload_libraries = 'pg_stat_statements'

# 2. 设置最大跟踪的 SQL 语句数量（默认 5000）
pg_stat_statements.max = 10000

# 3. 设置是否跟踪仅执行计划的语句
pg_stat_statements.track_planning = on

# 4. 设置跟踪级别（top 只跟踪顶层语句，all 包括嵌套语句）
pg_stat_statements.track = 'top'

# 5. 是否将不包含参数的语句也纳入统计
pg_stat_statements.save = on

[!CAUTION]

修改 shared_preload_libraries 后需要重启 PostgreSQL 服务。

pg_stat_statements 视图说明

列名	类型	说明
userid	oid	执行该语句的用户的 OID
dbid	oid	执行该语句的数据库的 OID
queryid	bigint	查询的内部哈希标识符
query	text	查询语句的文本（标准化后的形式）
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	读取块的总时间（毫秒）
blk_write_time	double precision	写入块的总时间（毫秒）
wal_records	bigint	生成的 WAL 记录数
wal_fpi	bigint	生成的 WAL 全页镜像数
wal_bytes	numeric	生成的 WAL 日志字节数

[!NOTE]

1. 调用次数较多的SQL

SELECT * FROM pg_stat_statements ORDER BY calls DESC LIMIT 10; -- 查看调用次数最多的前 10 个查询

1. 总执行时间较长的SQL

SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; -- 查看执行时间最长的前 10 个查询

1. 平均执行时间较长的SQL

SELECT * FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10; -- 查看平均执行时间最长的前 10 个查询

1. 在读/写块上总执行时间最多的SQL

SELECT * FROM pg_stat_statements ORDER BY (blk_read_time+blk_write_time) DESC LIMIT 10;

1. 在读/写块上平均执行时间最多的SQL

SELECT * FROM pg_stat_statements ORDER BY (blk_read_time+blk_write_time)/calls DESC LIMIT 10;

1. 查看时间抖动严重的SQL

SELECT * FROM pg_stat_statements ORDER BY stddev_exec_time DESC LIMIT 10;

1. 消耗共享内存较多的SQL

SELECT * FROM pg_stat_statements ORDER BY (shared_blks_hit+shared_blks_dirtied) DESC LIMIT 10;

1. 使用临时块较多的SQL

SELECT * FROM pg_stat_statements ORDER BY temp_blks_written DESC LIMIT 10;

1. 缓冲池命中率较低的SQL

SELECT *,cast(100.0*shared_blks_hit/nullif(shared_blks_hit + shared_blks_read,0) as decimal(10,2)) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

1. 查看 I/O 负载最高的查询

SELECT * FROM pg_stat_statements ORDER BY shared_blks_read DESC LIMIT 10; -- 查看读取块数最多的前 10 个查

1. 查看 WAL 生成量最大的查询

SELECT * FROM pg_stat_statements ORDER BY wal_bytes DESC LIMIT 10; -- 查看 WAL 写入量最大的前 10 个查询

[!TIP]

• 重置统计信息

-- 重置 pg_stat_statements 的所有统计信息
SELECT pg_stat_statements_reset();

-- 重置特定查询的统计信息（根据 queryid）
SELECT pg_stat_statements_reset('queryid_here');

• 查看统计信息汇总

-- 查看 pg_stat_statements 本身的统计信息
SELECT * FROM pg_stat_statements_info;