EXPLAIN и планы выполнения: что решает планировщик и почему

Dashboard-запрос занимает 6 секунд в production. Коллега спрашивает: «ты делал EXPLAIN?». Запускаешь EXPLAIN ANALYZE — и через две секунды видишь Seq Scan on orders ... actual rows=80000000. Один индекс. 200 мс. Вот для чего нужен EXPLAIN.

Что такое план выполнения

SQL декларативен — вы описываете какие строки хотите получить, а не как их извлечь. Планировщик Postgres заполняет этот пробел. Он читает SQL, обращается к pg_statistic, чтобы оценить количество строк для каждого условия, перебирает возможные комбинации типов сканирования и алгоритмов соединения, вычисляет стоимость каждого варианта и выбирает дешевейший. Результат — план — дерево операторов с прикреплёнными к ним оценками строк.

Запрос проходит четыре этапа до возврата первой строки:

1. Парсинг — текст преобразуется в дерево разбора
2. Переписывание — применяются правила и представления
3. Планирование — планировщик строит дешевейшее дерево выполнения
4. Исполнение — движок выполняет дерево

Единственный этап, который вы настраиваете, — это планирование. Всё остальное автоматично.

Чтение вывода

Простой план выглядит так:

Index Scan using idx_orders_workspace on orders
  (cost=0.43..14.2 rows=42 width=120)
  (actual time=0.08..1.1 rows=42 loops=1)
  Index Cond: (workspace_id = 42)

• cost=0.43..14.2 — стоимость запуска (первая строка) и полная стоимость в произвольных единицах. Не миллисекунды. Важны соотношения; абсолютные значения без контекста бессмысленны.
• rows=42 — оценка планировщика: сколько строк выдаст этот узел
• actual rows=42 — реально выданных строк во время выполнения
• loops=1 — сколько раз выполнялся этот узел (важно внутри соединений)

Диагностическое правило: сравнивайте rows (оценка) с actual rows (реальность). Разрыв в 10× — подозрительно. Разрыв в 1000× — вот почему запрос занимает 8 минут вместо 50 мс.

Метафора GPS

EXPLAIN — это предпросмотр маршрута в GPS перед поездкой. EXPLAIN ANALYZE — видеозапись реальной поездки с видеорегистратора. Предпросмотр говорит «маршрут займёт 12 минут» — это оценка по картам. Видеозапись говорит «поездка заняла 47 минут из-за пробок, которых не было на карте». Разрыв между оценкой и реальностью — это именно то место, где вы вмешиваетесь: плохие карты (устаревшая статистика) приводят к плохому выбору маршрута; плохие датчики (нет индекса для условия) делают поездку медленнее, чем обещал маршрут.

Конкретный сценарий: поиск узкого места

Поисковый запрос команды занимает 200 мс на staging и 8 секунд в production. EXPLAIN ANALYZE показывает:

Hash Join  (cost=2400..55000 rows=50000)
            (actual time=7800..8050 rows=42)
  Hash Batches: 64  Memory Usage: 2.1GB

Batches: 64 означает, что хеш-таблица 64 раза сбрасывалась на диск — результат того, что work_mem слишком мал для реального размера соединения. Исправление: SET work_mem = '64MB'. Тот же запрос — 220 мс. Один EXPLAIN, одно изменение конфигурации.