pg_stat_statements 三步定位慢查询：按总耗时排序，按缓存命中和 WAL 归因

一句话结论：ORDER BY mean_exec_time DESC 会把你的优化精力浪费在"偶发的慢"上。正确的排序是按 total_exec_time——它等于"单次耗时 × 调用次数"，也就是业务实际承受的总延迟。而判断"该改 SQL、改内存还是改写入模型"，只需要看三个字段组合：缓存命中、临时块落盘、WAL 字节数。

0. 先确认它真的在收集

pg_stat_statements 必须在 shared_preload_libraries 中预加载，这需要重启；同时查询标识符计算必须可用，compute_query_id 设为 auto 或 on 时会自动启用[1]。

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on

# 以下为官方文档给出的典型用法参数
pg_stat_statements.max = 10000        # 默认 5000
pg_stat_statements.track = all        # 默认 top
pg_stat_statements.track_planning = off

然后是扩展本身（每个库单独创建）：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

四个配置参数的默认值与代价（官方文档 F.32.4[1]）：

track_planning 那一行值得记住：它默认关闭不是疏忽，而是因为它在高并发同构语句场景下会放大争用。你要看"计划耗时"时才开，看完关掉。

权限：看不到别人的 SQL 不是 bug

官方明确：出于安全考虑，只有超级用户和拥有 pg_read_all_stats 角色的角色，才能看到其他用户执行的语句文本与 queryid[1]。

GRANT pg_read_all_stats TO monitoring;

1. 第一步：按总耗时排序

这是全文最重要的一条 SQL。

WITH tot AS (
  SELECT sum(total_exec_time) AS all_ms FROM pg_stat_statements
)
SELECT
  round((100.0 * s.total_exec_time / nullif(t.all_ms, 0))::numeric, 2) AS pct_of_total,
  s.calls,
  round(s.mean_exec_time::numeric, 2)                        AS mean_ms,
  round(s.max_exec_time::numeric, 2)                         AS max_ms,
  s.rows,
  round(100.0 * s.shared_blks_hit
        / nullif(s.shared_blks_hit + s.shared_blks_read, 0), 2) AS cache_hit_pct,
  s.temp_blks_written,
  pg_size_pretty(s.wal_bytes)                                AS wal,
  left(regexp_replace(s.query, '\s+', ' ', 'g'), 120)        AS query_head
FROM pg_stat_statements s
CROSS JOIN tot t
ORDER BY s.total_exec_time DESC
LIMIT 20;

字段全部来自官方视图定义[1]：total_exec_time（毫秒，总执行时间）、mean_exec_time、max_exec_time、calls、rows、shared_blks_hit/shared_blks_read、temp_blks_written、wal_bytes。

一个实测踩到的坑：第一版 SQL 我写的是 round(100.0 * s.total_exec_time / ..., 2)，在 PostgreSQL 18.6 上直接报错——

ERROR:  function round(double precision, integer) does not exist
HINT:   No function matches the given name and argument types.

原因：total_exec_time 是 double precision，而 PostgreSQL 的 round() 只有 round(numeric, int) 这一个两参数重载。必须显式转成 numeric，即上面那行 ::numeric。

注意对比：round(100.0 * s.shared_blks_hit / nullif(...), 2) 是合法的，因为 shared_blks_hit 是 bigint，100.0 × bigint 的结果是 numeric。同一个查询里两行 round 一个错一个对——这种坑只有真跑才会暴露。

为什么不是 mean_exec_time？举个抽象例子：一条 200ms 的报表查询每天跑 10 次，总耗时 2 秒；一条 4ms 的列表查询每秒跑 500 次，一天总耗时 172800 秒。后者才是 P0。total_exec_time 就是"业务等待的秒数"，它才是优化顺序。

2. 第二步：用三个字段组合归因

拿到 Top 20 之后，不要立刻看执行计划。先按下面这张表分类，因为分类决定你该改什么：

其中两个字段需要单独解释：

temp_blks_written：临时文件块写入量。它大于 0 说明这个语句至少有一次把排序或哈希写到了磁盘。注意 work_mem 是每个操作的上限，不是每会话总量——一个查询有 3 个排序节点，就可能用掉 3 份[2]。

wal_bytes：语句产生的 WAL 总量，类型是 numeric（不是 bigint，因为可能非常大）[1]。同组字段还有 wal_records、wal_fpi（全页镜像数）、wal_buffers_full。wal_fpi 异常高通常意味着检查点之后大量页面首次被修改，这是 checkpoint 周期与写入模式的问题，不是 SQL 写法问题。

顺带体检：统计本身是否在丢数据

SELECT dealloc, stats_reset FROM pg_stat_statements_info;

dealloc 是"因为观测到超过 pg_stat_statements.max 个不同语句，最少执行的条目被回收"的次数[1]。这个值持续增长时，你看到的 Top 20 是有偏的样本——先抬高 max（需重启）再谈优化。

3. 第三步：用 EXPLAIN 验证，而不是用直觉

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;   -- 把上一步选出的语句原文贴进来（注意把 $1 替换成真实值）

BUFFERS 是关键：它给出每个节点的 shared hit / read，与 pg_stat_statements 的 shared_blks_hit/shared_blks_read 一一对应，能直接确认"缓存未命中发生在哪个节点"。

三个必须看的信号：

1. rows 估算与实际差一个数量级 → 统计信息过期或选择性估算失真。先 ANALYZE 该表，仍不准再考虑 CREATE STATISTICS（注意：这类扩展统计不会被 pg_upgrade 迁移[3]）。
2. Sort Method: external merge → 与 temp_blks_written 互相印证，确认落盘。
3. Heap Fetches 很高（index-only scan 场景） → 需要 VACUUM，而不是加索引。

4. 三个由 queryid 引起的陷阱

官方对 queryid 的稳定性给了很长的警告，工程上要记住三条[1]：

1. 跨大版本不保证稳定。"不能假设 queryid 在 PostgreSQL 大版本之间保持稳定"[1]。所以不要把 queryid 硬编码进告警规则或基线对比脚本；用 query 文本做匹配，或在升级后重建基线。
2. 同文本可能分裂成多条。如果两条语句文本相同但语义不同（例如 search_path 不同），会被算作不同条目；反之，引用了"被删除后重建的函数"的两条语句会被算作不同条目[1]。
3. 表别名不同就是不同条目。"如果表别名不同，否则相似的查询会被视为不同"[1]。ORM 动态生成别名时，Top 20 会被稀释——先按 query 归一化再看总量。

另外两个容易踩的实现细节：

• query 文本存放在外部磁盘文件里，不占共享内存。极端情况下（大量超长文本累积，文件增长到难以管理）模块本身可能选择丢弃文本，导致视图中 query 变为 NULL，但每个 queryid 的统计值仍然保留[1]。这时应调小 pg_stat_statements.max 而不是调大。
• plans 与 calls 天然不等。计划统计与执行统计在各自结束时更新，且只对成功操作更新；用缓存计划时只有执行统计增长[1]。不要用 plans 判断"缓存命中率"。

5. 重置与基线的正确姿势

官方函数签名[1]：

pg_stat_statements_reset(userid oid, dbid oid, queryid bigint,
                         minmax_only boolean) returns timestamptz

三个实用模式：

-- 全量清零（发布前建立新基线）
SELECT pg_stat_statements_reset();

-- 只清某条语句（优化后单独复核）
SELECT pg_stat_statements_reset(0, 0, s.queryid)
FROM pg_stat_statements s
WHERE s.query LIKE 'SELECT%FROM orders WHERE customer_id%';

-- 只清 min/max，保留累计值（怀疑 max_exec_time 被历史尖峰污染时）
SELECT pg_stat_statements_reset(0, 0, 0, true);

minmax_only = true 时只重置 min_plan_time、max_plan_time、min_exec_time、max_exec_time，且 minmax_stats_since 会记录本次重置时间[1]。这是清理"某次尖峰导致 max_exec_time 永远难看"的正确方式，比全量清零安全得多。

基线对比时记得看 stats_since：它是该条目开始统计的时间，不同条目的起点不同，直接横向比较总量会失真[1]。

SELECT min(stats_since) AS oldest, max(stats_since) AS newest
FROM pg_stat_statements;

6. 本机实测记录（2026-11-09）

6.1 环境与负载设计

负载是故意构造的：让三类归因各有一个代表性样本，用来验证第 2 节那张判读表是否真的能从数据里分出类型。

6.2 真实输出

-- 即第 1 节的 SQL（已修正 round 类型）
WITH tot AS (SELECT sum(total_exec_time) AS all_ms FROM pg_stat_statements)
SELECT round((100.0*s.total_exec_time/nullif(t.all_ms,0))::numeric,1) AS pct,
       s.calls, round(s.mean_exec_time::numeric,2) AS mean_ms,
       round(s.max_exec_time::numeric,2) AS max_ms,
       round(100.0*s.shared_blks_hit/nullif(s.shared_blks_hit+s.shared_blks_read,0),1) AS hit_pct,
       s.temp_blks_written AS tmp_w, pg_size_pretty(s.wal_bytes) AS wal,
       left(regexp_replace(s.query,'\s+',' ','g'),50) AS q
FROM pg_stat_statements s CROSS JOIN tot t
ORDER BY s.total_exec_time DESC LIMIT 8;

 pct  | calls | mean_ms | max_ms | hit_pct | tmp_w  |   wal   |                        q
------+-------+---------+--------+---------+--------+---------+--------------------------------------------------
 78.1 | 16914 |    1.97 |  52.44 |   100.0 |      0 | 1596 kB | UPDATE pgbench_branches SET bbalance = bbalan...
 11.0 | 16914 |    0.28 |  30.32 |   100.0 |      0 | 1344 kB | UPDATE pgbench_tellers SET tbalance = tbalanc...
  7.7 |    20 |  163.72 | 176.29 |    19.8 | 124960 |  0 bytes| SELECT count(*) FROM (SELECT abalance FROM p...
  1.2 |     1 |  519.84 | 519.84 |   100.0 |      0 |  103 MB | copy pgbench_accounts from stdin with (freeze...
  1.2 | 16914 |    0.03 |   0.29 |    93.6 |      0 | 3969 kB | UPDATE pgbench_accounts SET abalance = abalan...
  0.3 |     1 |  141.53 | 141.53 |    14.6 |   2449 |   19 MB | alter table pgbench_accounts add primary key...
  0.2 | 17114 |    0.01 |   0.21 |   100.0 |      0 |  406 bytes| SELECT abalance FROM pgbench_accounts WHERE a...

6.3 用实测数据验证第 2 节的判读表

三条判读全部命中，说明这套"按 total_exec_time 排序 + 三字段归因"的方法是可用的。特别注意第 3 行：如果按 mean_exec_time 排序，它会排到第 1 位（163.72ms 远大于 1.97ms），但它只占 7.7% 的总耗时；而占 78.1% 的那个 UPDATE 在 mean 排序里根本进不了前三。这就是"排序维度决定优化顺序"的实证。

6.4 顺带验证的两个细节

 dealloc |         stats_reset
---------+-------------------------------
       0 | 2026-11-09 14:58:16.134544+00

• dealloc = 0：条目没有被回收，Top N 是可信样本（max 默认 5000 够用）。
• stats_reset 是 UTC 时间戳，与本地时区差 8 小时——做基线对比时注意别把时区差当成"数据缺失"。

7. 一张检查清单

附录：数据来源与检索时间

检索时间统一为 2026-11-09（Asia/Shanghai），抓取对象为 PostgreSQL 18.6 在线文档。

核验说明：所有参数默认值、视图列名与语义、函数签名、queryid 不稳定性条款均逐条比对官方文档（2026-11-09）。

第 6 节为本机实测（PostgreSQL 18.6，Docker/WSL2），其中的输出表格是真实命令的真实输出（只裁剪了列宽与行数）。此处有两件事值得强调：

1. 文中第 1 节的 SQL 在实测中第一次是跑不通的——round(double precision, integer) does not exist。已修正并验证，修正说明保留在正文里，因为它说明"文档给的样例写法"和"能跑的写法"之间确实有差距。
2. 实测确认了按 total_exec_time 与按 mean_exec_time 排序会给出完全不同的优化顺序（见 6.3 节第 3 行）。

第 1 节里"200ms / 每天 10 次 / 4ms / 每秒 500 次"仍然是用于说明排序逻辑的假设示例，不是测量结果；第 6 节的数字才是实测。