Detectar regressões de plano por queryid (comparação de snapshots do pg_stat_statements)

Comando

-- 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;

O que faz

Salva em uma tabela os contadores cumulativos do pg_stat_statements e os compara com o estado posterior por queryid; mostra o tempo médio e os buffers por chamada das chamadas feitas após o snapshot ao lado dos valores antigos.

Quando usar

Para encontrar a consulta cujo plano mudou após um deploy, upgrade major, atualização de estatísticas ou mudança de parâmetro ('ontem estava rápido, hoje está lento').

Atenção

Se pg_stat_statements_reset() for executado entre o snapshot e a comparação, ou se entradas forem descartadas por falta de espaço (dealloc), os deltas não têm sentido; verifique pg_stat_statements_info.stats_reset. Um aumento de buffers por chamada é um sinal mais confiável de mudança de plano do que um aumento de tempo, porque independe do cache. Para confirmar o plano, faça EXPLAIN da consulta com parâmetros reais ou use auto_explain; o PostgreSQL não tem fixação de plano nativa (é necessária a extensão pg_hint_plan). Remova a tabela de snapshot ao terminar.