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별로 비교한다. 스냅샷 이후 호출의 호출당 평균 시간과 buffer를 이전 값 옆에 보여 준다.

사용 시점

배포, 메이저 업그레이드, 통계 갱신, 파라미터 변경 후 계획이 바뀐 쿼리를 찾기 위해('어제는 빨랐는데 오늘은 느리다').

주의

스냅샷과 비교 사이에 pg_stat_statements_reset()이 실행되거나 공간 부족으로 항목이 제거되면(dealloc) delta 값은 의미가 없다. pg_stat_statements_info.stats_reset을 확인한다. 호출당 buffer 증가는 캐싱과 무관하므로 시간 증가보다 계획 변경의 더 신뢰할 만한 신호다. 계획을 확인하려면 실제 파라미터로 쿼리를 EXPLAIN하거나 auto_explain을 사용한다. PostgreSQL에는 내장 계획 고정 기능이 없다(pg_hint_plan 확장 필요). 끝나면 스냅샷 테이블을 삭제한다.