Planregressionen per queryid erkennen (Snapshot-Vergleich von pg_stat_statements)

Befehl

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

Was es tut

Speichert die kumulierten Zähler von pg_stat_statements in einer Tabelle und vergleicht sie je queryid mit dem späteren Stand; zeigt mittlere Zeit und Buffers je Aufruf der nach dem Snapshot erfolgten Aufrufe neben den alten Werten.

Wann einsetzen

Um die Abfrage zu finden, deren Plan sich nach einem Deploy, Major-Upgrade, Statistik-Refresh oder einer Parameteränderung geändert hat ('gestern schnell, heute langsam').

Vorsicht

Läuft pg_stat_statements_reset() zwischen Snapshot und Vergleich oder werden Einträge wegen Platzmangels verdrängt (dealloc), sind die Deltas bedeutungslos; prüfe pg_stat_statements_info.stats_reset. Ein Anstieg der Buffers je Aufruf ist ein zuverlässigeres Zeichen für eine Planänderung als ein Anstieg der Zeit, weil er unabhängig vom Caching ist. Um den Plan zu bestätigen, führe EXPLAIN mit echten Parametern aus oder nutze auto_explain; PostgreSQL hat kein eingebautes Plan-Pinning (die Extension pg_hint_plan ist nötig). Lösche die Snapshot-Tabelle, wenn du fertig bist.