生产事故中如何用 pg_stat_statements 找出真正昂贵的 PostgreSQL 查询

数据库变慢时，很多人会立即查询 pg_stat_statements，然后按平均耗时排序。但这往往会错过真正的问题：正在执行且尚未结束的查询还没有被统计，而一条单次只需几毫秒、每分钟却执行几十万次的 SQL，可能比偶尔出现的慢查询消耗更多资源。

更可靠的排查顺序是：先用 pg_stat_activity 判断此刻发生了什么，再从 pg_stat_statements 的累计值中切出一个观察窗口，最后通过执行计划、日志和历史监控解释原因。

第一步不是看历史，而是检查正在运行的会话

pg_stat_statements 记录的是已经完成的执行。一条此前从未出现、目前已经运行 30 秒但尚未结束的查询，不会及时出现在它的统计结果中。即使相同 SQL 以前执行过，累计指标也无法描述当前这一次执行正在等待什么。

事故现场应先运行下面的查询：

SELECT
    pid,
    query_id,
    usename,
    application_name,
    state,
    now() - xact_start  AS transaction_duration,
    now() - query_start AS query_duration,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
ORDER BY query_duration DESC;

重点关注这些信号：

• 查询或事务已经持续数秒甚至数分钟；
• 多个会话执行相同的 query_id；
• wait_event_type 显示锁、I/O 或其他等待；
• application_name、用户名或 SQL 文本指向临时报表、批处理或人工查询；
• 事务持续时间明显长于查询持续时间，可能存在长事务或应用未及时提交。

这一步是一次“现场检查”。如果异常查询仍在运行，应该围绕会话、锁和等待继续调查，而不是期待累计统计直接给出答案。

pg_stat_statements 没有时间轴，必须自己切出窗口

pg_stat_statements 中的 calls、total_exec_time、rows 和块读写指标都是累计值。单次查询只能告诉你自上次重置以来发生了什么，不能直接回答“最近 30 秒是谁消耗了最多时间”。

没有现成监控平台时，较安全的做法是在同一个数据库会话中获取两次快照，然后计算差值。下面的脚本适用于包含 total_exec_time 列的较新 PostgreSQL 版本；运行前应确认已经安装并启用 pg_stat_statements。

CREATE TEMP TABLE pgss_before AS
SELECT * FROM pg_stat_statements;

-- 按业务周期调整窗口，例如 10、30 或 60 秒。
SELECT pg_sleep(30);

CREATE TEMP TABLE pgss_after AS
SELECT * FROM pg_stat_statements;

SELECT
    a.queryid,
    d.datname,
    r.rolname,
    a.calls - COALESCE(b.calls, 0) AS calls_delta,
    round(
        (a.total_exec_time - COALESCE(b.total_exec_time, 0))::numeric,
        2
    ) AS exec_time_delta_ms,
    a.rows - COALESCE(b.rows, 0) AS rows_delta,
    a.shared_blks_read - COALESCE(b.shared_blks_read, 0)
        AS shared_reads_delta,
    a.temp_blks_written - COALESCE(b.temp_blks_written, 0)
        AS temp_written_delta,
    left(a.query, 160) AS query
FROM pgss_after AS a
LEFT JOIN pgss_before AS b
       ON b.userid = a.userid
      AND b.dbid = a.dbid
      AND b.queryid = a.queryid
JOIN pg_database AS d ON d.oid = a.dbid
JOIN pg_roles AS r ON r.oid = a.userid
WHERE a.calls > COALESCE(b.calls, 0)
ORDER BY exec_time_delta_ms DESC
LIMIT 20;

这里使用 LEFT JOIN，因此观察窗口内首次出现的语句也能进入结果。已经被淘汰、重置或从第二次快照消失的记录仍需要单独处理；这也是自行长期保存统计时容易踩坑的地方。

窗口长度不能机械设定。高频 API 可能观察 10～30 秒就足够，低频报表或定时任务则可能需要等待数分钟甚至一小时。只看一次短窗口，容易错过偶发但代价很高的任务。

排序方式决定你能看到哪一种问题

找到差值后，不要只盯着平均执行时间。不同指标回答的是不同问题：

如果已经有一个干净的观察窗口，可以用同一份查询反复更换排序条件：

-- 总体工作量最大
ORDER BY total_exec_time DESC;

-- 调用最频繁
ORDER BY calls DESC;

-- 排除样本过少后，寻找单次执行很慢的语句
WHERE calls >= 10
ORDER BY mean_exec_time DESC;

-- 读取共享块最多
ORDER BY shared_blks_read DESC;

-- 临时文件写入最多，可能发生排序或哈希溢写
ORDER BY temp_blks_written DESC;

“最慢”不等于“最值得先优化”。例如：

查询 B 在慢查询榜上更醒目，但查询 A 的总体消耗是它的 100 倍。优化优先级应结合频率、单次成本、资源读写和业务重要性，而不能只看一个平均值。

重置统计可以制造干净窗口，但要非常谨慎

在可重复、高流量的事故中，也可以先重置统计，再观察接下来几分钟的负载：

SELECT pg_stat_statements_reset();

SELECT dealloc, stats_reset
FROM pg_stat_statements_info;

随后查询当前数据库中的主要语句：

SELECT
    pgss.queryid,
    d.datname,
    r.rolname,
    pgss.calls,
    round(pgss.total_exec_time::numeric, 2) AS total_exec_ms,
    round(pgss.mean_exec_time::numeric, 2)  AS mean_exec_ms,
    pgss.rows,
    pgss.shared_blks_read,
    pgss.temp_blks_written,
    left(pgss.query, 160) AS query
FROM pg_stat_statements AS pgss
JOIN pg_database AS d ON d.oid = pgss.dbid
JOIN pg_roles AS r ON r.oid = pgss.userid
WHERE pgss.dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY pgss.total_exec_time DESC
LIMIT 20;

重置是侵入性操作：它会清除现有累计指标和查询记录。稀有故障、尚未导出历史、多个团队共享统计或监控系统依赖当前计数时，不应贸然执行。优先使用双快照差值；只有明确接受历史丢失，并确认权限和影响后，才考虑重置。

pg_stat_statements 负责定位，执行计划负责解释

排在榜首只说明一条 SQL 值得调查，并不说明它为什么昂贵。选出目标后，可以进一步检查执行计划：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT *
FROM orders
WHERE customer_id = 12345
ORDER BY created_at DESC
LIMIT 50;

运行前必须替换成真实查询及安全参数。ANALYZE 会真正执行语句；对 UPDATE、DELETE、INSERT 或极其昂贵的查询，应优先在测试环境、只读副本或受控事务中验证。生产环境还可以结合 auto_explain 捕获查询执行当时的计划，并用数据库日志补充调用上下文。

监控工具真正提供的是历史，而不只是排行榜

长期方案需要定期抓取快照、计算增量并把结果保存在 pg_stat_statements 之外。选择工具时应确认：

• 是否保留完整或足够长的标准化 SQL 文本；
• 抓取间隔能否覆盖业务峰值和周期任务；
• 是否正确处理统计重置、记录淘汰和新 query_id；
• 能否按总时间、调用频率、块读取和临时写入切换视角；
• 能否把查询指标与 CPU、I/O、锁等待和部署时间关联起来；
• 数据库托管平台和其他代理是否也在高频读取该视图。

最后一点容易被忽略。云服务提供商、APM 和多个数据库监控代理可能同时抓取 pg_stat_statements。过多或过于频繁的采集会增加额外开销，甚至形成该模块上的轻量级锁竞争。监控不是越多越好，应明确唯一的数据采集责任，或至少协调采样频率。

一份可执行的事故排查清单

1. 查询 pg_stat_activity，检查仍在运行的长查询、长事务和等待事件。
2. 没有历史平台时，获取两次 pg_stat_statements 快照并计算差值。
3. 分别按总执行时间、调用次数、平均耗时、块读取和临时写入排序。
4. 不要把“单次最慢”直接等同于“总体危害最大”。
5. 仅在能够接受历史清零时调用 pg_stat_statements_reset()。
6. 对候选 SQL 使用 EXPLAIN (ANALYZE, BUFFERS)、auto_explain 和日志解释原因。
7. 部署持续监控，用历史判断这是新回归、周期任务，还是长期存在的热点。

pg_stat_statements 最擅长回答“下一条应该调查哪条 SQL”，而不是独立回答“它为什么慢”。把实时会话、窗口差值、执行计划和时间序列历史串起来，才能从事故现场的猜测走向可验证的优化。