按 queryid 捕获计划回归（pg_stat_statements 快照对比）

命令

-- 1) Take a reference snapshot (before deploy/upgrade/ANALYZE)
CREATE TABLE IF NOT EXISTS pgss_snapshot AS
  SELECT now() AS snap_time, userid, dbid, queryid, calls, total_exec_time,
         shared_blks_hit, shared_blks_read, left(query, 200) AS query
  FROM pg_stat_statements WITH NO DATA;
INSERT INTO pgss_snapshot
  SELECT now(), userid, dbid, queryid, calls, total_exec_time,
         shared_blks_hit, shared_blks_read, left(query, 200)
  FROM pg_stat_statements;

-- 2) Later: queries whose time per call grew the most since the snapshot
WITH base AS (
  SELECT DISTINCT ON (userid, dbid, queryid) *
  FROM pgss_snapshot
  ORDER BY userid, dbid, queryid, snap_time DESC
)
SELECT s.queryid,
       s.calls - b.calls AS new_calls,
       round((b.total_exec_time / b.calls)::numeric, 2) AS old_mean_ms,
       round(((s.total_exec_time - b.total_exec_time) / (s.calls - b.calls))::numeric, 2) AS new_mean_ms,
       round((b.shared_blks_hit + b.shared_blks_read)::numeric / b.calls, 0) AS old_blks_per_call,
       round((s.shared_blks_hit + s.shared_blks_read - b.shared_blks_hit - b.shared_blks_read)::numeric
             / (s.calls - b.calls), 0) AS new_blks_per_call,
       left(regexp_replace(s.query, '\s+', ' ', 'g'), 100) AS query
FROM pg_stat_statements s
JOIN base b USING (userid, dbid, queryid)
WHERE s.calls - b.calls >= 10
  AND b.calls > 0
ORDER BY ((s.total_exec_time - b.total_exec_time) / (s.calls - b.calls))
         / NULLIF(b.total_exec_time / b.calls, 0) DESC NULLS LAST
LIMIT 20;

-- 3) Is the suspicious queryid running right now?
SELECT pid, state, now() - query_start AS runtime, left(query, 100) AS query
FROM pg_stat_activity
WHERE query_id = -4094682686355101727;

作用

将 pg_stat_statements 的累计计数器保存到一张表中，并按 queryid 与之后的状态进行比较；将快照之后调用的每次调用平均时间和缓冲区与旧值并列显示。

适用场景

用于找出在发布、大版本升级、统计信息刷新或参数变更后计划发生变化的查询（"昨天还很快，今天变慢"）。

注意

如果在快照和比较之间运行了 pg_stat_statements_reset()，或条目因空间不足被淘汰（dealloc），则差值没有意义；请检查 pg_stat_statements_info.stats_reset。每次调用的缓冲区增加比时间增加更能可靠地说明计划发生了变化，因为它与缓存无关。要确认计划，请用真实参数 EXPLAIN 该查询或使用 auto_explain；PostgreSQL 没有内置的计划固定功能（需要 pg_hint_plan 扩展）。完成后删除快照表。