Выявить регрессии планов по 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). По завершении удалите таблицу снимка.