Detectar regresiones de plan por queryid (comparación de snapshots de 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;

Qué hace

Guarda en una tabla los contadores acumulados de pg_stat_statements y los compara por queryid con el estado posterior; muestra, junto a los valores antiguos, el tiempo medio y los buffers por llamada de las llamadas hechas después del snapshot.

Cuándo usarlo

Para encontrar la consulta cuyo plan cambió tras un despliegue, un upgrade mayor, una actualización de estadísticas o un cambio de parámetros ('ayer iba rápido, hoy va lento').

Precaución

Si se ejecuta pg_stat_statements_reset() entre el snapshot y la comparación, o se descartan entradas por falta de espacio (dealloc), los deltas no tienen sentido; revise pg_stat_statements_info.stats_reset. Un aumento de los buffers por llamada es una señal de cambio de plan más fiable que un aumento del tiempo, porque no depende del cache. Para confirmar el plan, haga EXPLAIN de la consulta con parámetros reales o use auto_explain; PostgreSQL no tiene fijación de planes integrada (se requiere la extensión pg_hint_plan). Elimine la tabla de snapshot al terminar.