Оптимизация запросов через EXPLAIN ANALYZE

Этот материал логично продолжает гайд «Индексы в PostgreSQL: типы, мониторинг и типичные ошибки»: здесь вы научитесь читать план выполнения и использовать EXPLAIN ANALYZE как основной инструмент диагностики медленных запросов — без догадок и «магических» настроек.

1. Зачем нужен EXPLAIN

PostgreSQL строит план — дерево операций (сканирование таблиц, соединения, сортировки). Команда EXPLAIN показывает оценку планировщика: ожидаемые строки, стоимость, порядок узлов.

EXPLAIN ANALYZE выполняет запрос и добавляет фактические времена и число прочитанных строк. Для INSERT/UPDATE/DELETE в проде удобно оборачивать в BEGIN … ROLLBACK, если нужен только план без побочных эффектов.

Важно: на продакшене для тяжёлых запросов ANALYZE создаёт реальную нагрузку. Запускайте в непик, с лимитом по данным (LIMIT в подзапросе) или на копии/стенде.

2. Как читать строку узла плана

Типичный фрагмент:

Index Scan using idx_orders_user_id on orders  (cost=0.42..8.44 rows=1 width=36) (actual time=0.015..0.016 rows=1 loops=1)

• Узел — Index Scan, имя индекса, таблица.
• cost=startup..total — условные единицы стоимости (не секунды). Сравнивать полезно между вариантами одного запроса.
• rows — ожидаемое число строк на один проход узла.
• width — оценка среднего размера строки в байтах.
• actual time=первый_ряд..все_ряды — реальное время в миллисекундах.
• loops — сколько раз узел выполнялся (важно для вложенных циклов: в выводе rows часто на один loop, итог ≈ rows * loops).

С BUFFERS появятся shared hit/read, temp read/written — признаки кэша, диска и спилла во временные файлы.

3. Основные типы узлов (кратко)

Понимание узлов напрямую связано с индексами и статистикой — см. снова гайд по индексам.

4. Практический чек-лист оптимизации

1. Соберите план: EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <ваш_запрос>;
2. Найдите узлы с наибольшим actual time (с учётом loops).
3. Сравните оценочные и фактические rows — большой разрыв часто значит устаревшую статистику или сложные предикаты. Тогда: ANALYZE, CREATE STATISTICS, переписывание условия.
4. Проверьте буферы: много read при малом hit на горячих запросах — память/кэш или объём данных.
5. Ищите Seq Scan при узкой выборке — кандидат на индекс или пересмотр WHERE.
6. Спилл во временные файлы — точечно work_mem в сессии для тяжёлого отчёта, не «на всегда» глобально без измерений.
7. После изменения — снова полный EXPLAIN ANALYZE и сравнение.

5. Полезные варианты EXPLAIN

• EXPLAIN (FORMAT JSON) — для визуализаторов и скриптов.
• auto_explain в postgresql.conf — логировать планы дольше порога (следите за объёмом логов).
• pg_stat_statements — найти топ по суммарному времени, затем копать конкретный текст через EXPLAIN.

6. Типичные ошибки

7. Мини-пример

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.created_at
FROM orders o
WHERE o.user_id = 42
  AND o.created_at > now() - interval '30 days';

Проверьте: есть ли индекс по user_id / (user_id, created_at), насколько rows совпадают с реальностью, нет ли лишнего Sort или тяжёлого Bitmap Heap Scan из‑за селективности.

См. также

• Индексы в PostgreSQL
• Партиционирование и шардирование
• Мониторинг: Prometheus и Grafana