Диагностика медленных запросов (pg_stat_statements)

Действуй как DBA, разбирающий инцидент «база тормозит». Стек: {{engine}}. Симптом: {{symptom}}.

Алгоритм (сверху вниз — от обзора к фиксу)

1. Кто виноват — top offenders

SELECT
  substring(query, 1, 80) AS q,
  calls,
  round(mean_exec_time::numeric, 1) AS mean_ms,
  round(total_exec_time::numeric / 1000, 1) AS total_sec,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Сортируй по total_exec_time, не по mean_exec_time. Запрос на 5ms × 10M вызовов хуже чем 2s × 10 вызовов.

Если pg_stat_statements не включён — добавь в shared_preload_libraries, перезапусти, CREATE EXTENSION pg_stat_statements.

2. Lock contention

SELECT
  blocked.pid AS blocked_pid, blocked.query AS blocked_query,
  blocking.pid AS blocking_pid, blocking.query AS blocking_query,
  blocked.wait_event_type, blocked.wait_event
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Признаки: idle in transaction висит долго, wait_event = transactionid / tuple, latency p99 скачет без роста QPS. Чинить: убить виновника pg_terminate_backend(pid), потом разбираться почему транзакция держалась.

3. Свежесть статистики

SELECT relname,
  n_live_tup, n_dead_tup,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 1) AS dead_pct,
  last_analyze, last_autoanalyze, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 20;

Симптомы: оптимизатор оценивает 100 строк, реально 100k → Nested Loop вместо Hash Join → план разваливается. Фикс: ANALYZE table ручной прогон. Если регулярно — поднять autovacuum_analyze_scale_factor / снизить autovacuum_analyze_threshold для проблемных таблиц.

4. Параметр-сниффинг / план не меняется на разные параметры

Симптом: один и тот же запрос с разными ? параметрами иногда летает, иногда висит. Корень: prepared statement закешировал generic plan под одну селективность. Фикс:

• plan_cache_mode = 'force_custom_plan' для сессии
• prepared_statement_threshold в PgBouncer
• DISCARD PLANS после миграций

5. Cache hit ratio (кеш буферов)

SELECT
  round(100.0 * sum(blks_hit) / NULLIF(sum(blks_hit + blks_read), 0), 2) AS cache_hit_pct
FROM pg_stat_database WHERE datname = current_database();

Норма для OLTP: ≥99%. Меньше — поднимай shared_buffers (обычно 25% RAM) или индексы плохо помещаются в память. Смотри в pg_statio_user_tables где конкретно идёт чтение с диска.

6. Долгие транзакции

SELECT pid, state, xact_start, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle' AND xact_start IS NOT NULL
ORDER BY xact_start ASC LIMIT 10;

Долгая транзакция блокирует autovacuum, держит row versions, раздувает таблицы. Поставь idle_in_transaction_session_timeout = '5min'.

Как читать EXPLAIN (краткая шпаргалка)

Что должно насторожить:

• Rows Removed by Filter высокий → индекс не покрывает условие
• actual rows ≫ estimated rows → статистика устарела или плохая корреляция
• Sort Method: external merge Disk → work_mem мал
• Heap Fetches высокий в Index Only Scan → нужен VACUUM (visibility map не свежая)
• loops=N где N большой в Nested Loop → плохой join order, см. extended statistics

Что фиксить в каком порядке

1. Lock contention сейчас — пользователи видят 503
2. Top-3 по total_exec_time — самый высокий ROI
3. Stale statistics — может починить десяток запросов одним ANALYZE
4. Missing index — если EXPLAIN показывает Seq Scan на горячей таблице
5. shared_buffers / work_mem — после того как запросы оптимизированы

Анти-паттерны

• ❌ Сортировать pg_stat_statements по mean_exec_time — пропустишь массовых убийц
• ❌ Добавлять индексы наугад без EXPLAIN до/после — индексы тоже стоят
• ❌ Поднимать shared_buffers до 80% RAM — OS-кеш не получает память, IO растёт
• ❌ Игнорировать idle in transaction — главный источник тихих инцидентов
• ❌ RESET pg_stat_statements без снимка до — потеряешь baseline для сравнения