PG数据库快速安装并启用pg_stat_statements 附常用性能查询SQL脚本
pg_stat_statements 是 PostgreSQL 的一个官方扩展（Extension），被誉为“DBA 和开发人员的瑞士军刀”。它的核心功能是追踪服务器执行的所有 SQL 语句的统计信息，例如执行次数、总耗时、返回行数、磁盘 I/O 等。
通过分析这些数据，你可以快速定位数据库的性能瓶颈，找出哪些 SQL 语句最耗时、最耗资源，从而进行有针对性的优化。
如何安装和启用
编译并安装 pg_stat_statements（适用于源码安装）：
-- 进入 PostgreSQL 源码目录
cd /opt/postgresql-17.4/contrib/pg_stat_statements
-- 编译安装
sudo make && sudo make install在postgresql.conf配置文件中增加如下参数
# pg_stat_statements 配置
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000 # 最多记录 10000 条不同查询
pg_stat_statements.track = all # 跟踪所有语句（包括函数内）
pg_stat_statements.track_utility = off # 不跟踪非执行语句（如 CREATE, COMMENT）
track_activities = on # 确保开启（默认一般为 on）
track_counts = on # 启用统计收集（默认 on）重启postgresql服务后加载 shared_preload_libraries
systemctl restart postgresql登录postgres控制台在需要监控的数据库中创建扩展
psql -h 10.0.0.5 -U postgres -p 54321连接到你要监控的数据库如testdb，然后执行：
-- 在需要监控的数据库中创建
\c testdb
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;shared_preload_libraries常用查询SQL：
-- 查看最耗时的 TOP 5 查询
SELECT
query,
calls,
total_exec_time,
mean_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;
-- 平均最慢的 SQL（每次执行最慢）
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
-- 查看调用最频繁的 TOP 5 查询
SELECT
query,
calls,
total_exec_time,
mean_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;
-- 查看平均耗时最长的 TOP 5 查询：
SELECT
query,
calls,
total_exec_time,
mean_exec_time
FROM pg_stat_statements
WHERE calls > 100 -- 避免那些只执行一两次的查询干扰
ORDER BY mean_exec_time DESC
LIMIT 5;
-- 查询IO最高的SQL
SELECT
query,
shared_blks_read + local_blks_read AS total_blocks_read,
shared_blks_hit + local_blks_hit AS cache_hits,
(shared_blks_hit::float / NULLIF(shared_blks_hit + shared_blks_read, 0)) AS hit_ratio
FROM pg_stat_statements
ORDER BY total_blocks_read DESC
LIMIT 10;
-- 查看物理读（磁盘 I/O）最高的查询
SELECT
query,
calls,
total_exec_time,
shared_blks_read, -- 物理读（磁盘）
shared_blks_hit, -- 逻辑读（缓存）
(shared_blks_read + shared_blks_hit) AS total_read,
round(100.0 * shared_blks_hit / nullif(shared_blks_read + shared_blks_hit, 0), 2) AS cache_hit_ratio
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;
-- 查看产生最多“脏数据”的查询（写操作源头）
SELECT
query,
calls,
shared_blks_dirtied
FROM pg_stat_statements
ORDER BY shared_blks_dirtied DESC
LIMIT 10;
-- 写 WAL 最多的 SQL（写入压力大）
SELECT
query,
calls,
wal_bytes,
wal_records
FROM pg_stat_statements
ORDER BY wal_bytes DESC
LIMIT 10;
-- 查看每个数据库的 I/O 情况
SELECT
datname AS database_name,
blks_read, -- 物理读
blks_hit, -- 缓存读
round(100.0 * blks_hit / nullif(blks_read + blks_hit, 0), 2) AS cache_hit_ratio
FROM pg_stat_database;
-- 找到被全表扫描最多的表（可能缺少索引）
SELECT
schemaname,
relname AS table_name,
seq_scan,
seq_tup_read,
idx_scan
FROM pg_stat_all_tables
ORDER BY seq_tup_read DESC -- 按全表扫描读取的行数排序
LIMIT 10;
-- 找到最“热”的表（读写最频繁）
SELECT
schemaname,
relname AS table_name,
n_tup_ins + n_tup_upd + n_tup_del AS total_writes,
seq_tup_read + idx_tup_fetch AS total_reads
FROM pg_stat_all_tables
ORDER BY total_writes + total_reads DESC
LIMIT 10;
-- 重置统计信息（比如在优化后想重新开始计数）
SELECT pg_stat_statements_reset();pg_stat_statements 核心功能与能提供的信息字段说明：
userid 执行该语句的用户 OID（可与 pg_user视图关联）
dbid 执行该语句的数据库 OID（可与 pg_database视图关联）
queryid 一个哈希值，代表同一种标准化后的 SQL 语句
query 标准化后的 SQL 语句文本（见下文“标准化”解释）
calls 该语句被执行的总次数
total_exec_time 该语句总耗时（单位：毫秒）
min_exec_time 该语句单次最短耗时（单位：毫秒）
max_exec_time 该语句单次最长耗时（单位：毫秒）
mean_exec_time 该语句平均耗时（单位：毫秒）
stddev_exec_time 耗时的标准差，体现执行时间的稳定性
rows 该语句返回或影响的总行数
shared_blks_hit 从共享缓存（内存）中命中的块数（高效）
shared_blks_read 从磁盘读取的块数（低效，I/O 瓶颈）
shared_blks_dirtied 被该语句弄脏（修改）的块数
shared_blks_written 由后台写入器写出的块数还有其它如临时块、本地块相关的统计信息
更多详细介绍可参考：F.30. pg_stat_statements — 跟踪SQL规划和执行的统计信息
Tag标签:「PostgreSQL 军刀 性能 优化 pg_stat_statements 扩展 Extension 瑞士 耗时」更新时间:「2025-08-27 20:45:11」阅读次数:「22」