Détecter les régressions de plan par queryid (comparaison de snapshots pg_stat_statements)

Commande

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

Ce que fait la commande

Enregistre dans une table les compteurs cumulés de pg_stat_statements et les compare plus tard à l'état courant par queryid ; affiche le temps moyen et les buffers par appel des appels effectués après le snapshot à côté des anciennes valeurs.

Quand l'utiliser

Pour trouver la requête dont le plan a changé après un déploiement, une mise à niveau majeure, un rafraîchissement des statistiques ou un changement de paramètre ('hier c'était rapide, aujourd'hui c'est lent').

Attention

Si pg_stat_statements_reset() s'exécute entre le snapshot et la comparaison, ou si des entrées sont évincées faute de place (dealloc), les deltas n'ont aucun sens ; vérifiez pg_stat_statements_info.stats_reset. Une hausse des buffers par appel est un signe de changement de plan plus fiable qu'une hausse du temps, car elle ne dépend pas du cache. Pour confirmer le plan, faites un EXPLAIN de la requête avec les vrais paramètres ou utilisez auto_explain ; PostgreSQL n'a pas de figeage de plan natif (l'extension pg_hint_plan est nécessaire). Supprimez la table de snapshot une fois terminé.