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拡張が必要)。終了後はスナップショット表を削除する。