Plan regresyonunu queryid ile yakala (pg_stat_statements snapshot karşılaştırması)

Komut

-- 1) Referans snapshot al (deploy/upgrade/ANALYZE öncesi)
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) Sonra: snapshot'tan bu yana çağrı başına süresi en çok artan sorgular
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) Şüpheli queryid şu anda çalışıyor mu?
SELECT pid, state, now() - query_start AS runtime, left(query, 100) AS query
FROM pg_stat_activity
WHERE query_id = -4094682686355101727;

Ne yapar?

pg_stat_statements'ın kümülatif sayaçlarını tabloya kaydeder ve sonraki durumla queryid bazında karşılaştırır; snapshot sonrasındaki çağrıların ortalama süresini ve çağrı başına buffer sayısını eski değerlerle yan yana gösterir.

Ne zaman kullanılır?

Deploy, major upgrade, istatistik yenilemesi veya parametre değişikliği sonrası 'dün hızlıydı, bugün yavaş' şikâyetinde planı değişen sorguyu bulmak için.

Dikkat

Snapshot ile karşılaştırma arasında pg_stat_statements_reset() çalışırsa veya girdi yer kalmadığı için atılırsa (dealloc) farklar anlamsızlaşır; pg_stat_statements_info.stats_reset'i kontrol et. Çağrı başına buffer sayısındaki artış, cache etkisinden bağımsız olduğu için süre artışından daha güvenilir bir plan değişikliği işaretidir. Planı doğrulamak için sorguyu gerçek parametrelerle EXPLAIN et veya auto_explain kullan; PostgreSQL'de yerleşik plan sabitleme yoktur (pg_hint_plan eklentisi gerekir). Snapshot tablosunu iş bitince sil.