EXPLAIN мы уже видели вскользь несколько раз. Теперь — детально. Цель этого урока: научиться читать план запроса так, чтобы по нему сразу понимать, что происходит, и где у запроса слабое место. Это базовый навык middle SQL-разработчика, и без него никакая оптимизация невозможна.

Без EXPLAIN любой разговор о перфомансе превращается в шаманство: «давайте добавим индекс, может, поможет». С EXPLAIN ты видишь, что именно происходит: какие узлы тяжёлые, где статистика врёт, помогает ли индекс. Это самый ценный навык в работе с БД после самого SQL.

Урок будет насыщенный — мы пройдём через десяток типов узлов плана, разберём формат вывода и поговорим о том, как читать actual time vs rows. Если что-то покажется сложным — это нормально, к плану возвращаешься в проде десятки раз, и с практикой структура становится естественной.

EXPLAIN vs EXPLAIN ANALYZE

Сначала — два режима команды:

• EXPLAIN <query> — показывает план, не выполняя запрос. Все числа в плане — оценки оптимизатора на основе статистики таблиц. Очень дешёво.
• EXPLAIN ANALYZE <query> — выполняет запрос и показывает план с фактическими числами: реальное время каждого узла, реальное число прочитанных строк. Дорого (запрос реально выполнится), зато точно.

Правило: для понимания структуры — EXPLAIN. Для серьёзного перфоманс-разбора — EXPLAIN ANALYZE, иначе можно гоняться за фантомами в оценках.

Опасный нюанс: EXPLAIN ANALYZE DELETE FROM ... реально удалит данные. Чтобы посмотреть план без побочных эффектов:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'cancelled';
ROLLBACK;

ROLLBACK откатит изменения, а вывод EXPLAIN ANALYZE останется в логах сессии.

План — это дерево

Главное концептуальное наблюдение: вывод EXPLAIN — это перевёрнутое дерево операций. Корень — самая верхняя строка, листья — самые вложенные. Данные текут снизу вверх: листья читают таблицы, родители их соединяют/фильтруют/агрегируют.

Пример вывода:

HashAggregate  (cost=15.20..15.80 rows=12 width=40)
  Group Key: c.full_name
  ->  Hash Right Join  (cost=1.27..14.40 rows=20 width=22)
        Hash Cond: (o.customer_id = c.id)
        ->  Seq Scan on orders o  (cost=0.00..1.20 rows=20 width=4)
        ->  Hash  (cost=1.12..1.12 rows=12 width=22)
              ->  Seq Scan on customers c  (cost=0.00..1.12 rows=12 width=22)

Читаем снизу вверх:

1. Seq Scan on customers — прочитали всех клиентов.
2. Hash — построили хеш-таблицу по c.id.
3. Seq Scan on orders — прочитали все заказы.
4. Hash Right Join — для каждого заказа нашли клиента в хеш-таблице.
5. HashAggregate — сгруппировали результат по full_name, посчитали COUNT.

Стрелочки -> показывают «вход в узел». Отступ — глубина в дереве. С этой структурой в голове любой план становится читаемым.

Числа в плане

В каждой строке плана PostgreSQL пишет несколько чисел в скобках:

(cost=1.27..14.40 rows=20 width=22)

• cost=A..B — оценочная стоимость в условных единицах PostgreSQL. A — стоимость первой строки (startup cost), B — стоимость всех строк (total cost). Это безразмерные числа, важно только их отношение между планами, не абсолют.
• rows=N — оценочное число строк, которые вернёт узел.
• width=W — оценочный размер одной строки в байтах.

После EXPLAIN ANALYZE появятся ещё:

• actual time=X..Y — реальное время в миллисекундах: первая и все строки.
• rows=N — реальное число строк.
• loops=M — сколько раз узел был выполнен (важно для Nested Loop, где внутренний узел запускается на каждой строке внешнего).

Estimated vs actual: главный тревожный сигнал

Когда EXPLAIN ANALYZE показывает план, ты увидишь и оценки (rows=20), и факты (actual rows=18). Если они расходятся в разы или порядки — это

->  Seq Scan on orders  (cost=0.00..1.20 rows=20 width=4) (actual rows=180000 ...)

Тут оценка — 20 строк, факт — 180 тысяч. Оптимизатор спланировал под мизерные данные, а получил гору — наверняка выбрал Nested Loop там, где был бы лучше Hash Join. Что делать:

1. Запустить ANALYZE <table> — обновить статистику.
2. Проверить, нет ли в запросе сложных предикатов, которые планировщик плохо оценивает (функции, выражения, IN с большим списком).
3. В крайнем случае — рассмотреть CREATE STATISTICS для многоколоночных корреляций.

Опции EXPLAIN

EXPLAIN принимает несколько опций в скобках:

• EXPLAIN (ANALYZE) — выполнить запрос, дать фактические числа.
• EXPLAIN (BUFFERS) — показать чтения страниц (hit/read). Требует ANALYZE.
• EXPLAIN (VERBOSE) — больше деталей: полные имена колонок, output schema каждого узла.
• EXPLAIN (FORMAT JSON) — план в JSON для машинной обработки (например, explain.depesz.com).
• EXPLAIN (SETTINGS) — какие настройки оптимизатора отличаются от дефолта (полезно для дебага «почему план странный»).

В реальной жизни самая частая комбинация — EXPLAIN (ANALYZE, BUFFERS). Она даёт всё необходимое для разбора. Для очень тяжёлых запросов добавляют FORMAT JSON и грузят в визуализаторы.

Главные узлы плана

Это инвентарь, который нужно знать.

Сканирование таблицы

• Seq Scan — последовательное чтение всей таблицы. На больших таблицах — плохо, если фильтр селективный. На маленьких — нормально.
• Index Scan — спуск по индексу до нужных ключей, для каждого — чтение строки из heap.
• Index Only Scan — то же, но строка из heap не читается: все нужные данные уже есть в индексе. Очень быстро. Возможен, если все колонки SELECT есть в индексе (или через INCLUDE), И страницы heap «помечены как видимые» (visibility map). Регулярный VACUUM поддерживает это.
• Bitmap Index Scan + Bitmap Heap Scan — двухфазный вариант: сначала по индексу строится битовая карта страниц, потом heap читается за один проход по карте. Хорошо для средних по селективности фильтров и OR-условий.

Соединения

• Nested Loop — для каждой строки из левого входа берётся правый и фильтруется. Если правый вход — Index Scan, это очень быстрый план для маленького левого набора. Если правый — Seq Scan, и левый набор не крошечный, это катастрофа: $O(N \times M)$.
• Hash Join — строится хеш-таблица по одному входу (обычно меньшему), второй идёт через хеш. $O(N + M)$. Лучший вариант для средних объёмов без индексов.
• Merge Join — оба входа отсортированы по join-ключу; идём по обоим как при слиянии. Хорош, если данные уже отсортированы (по индексу), плохо если приходится сортировать.

Прочие узлы

• Sort — сортировка результата. Если объём большой — в PostgreSQL это может вылиться в external sort на диск (work_mem не хватило). В EXPLAIN ANALYZE видно: Sort Method: external merge Disk: ...kB.
• HashAggregate / GroupAggregate — два способа GROUP BY. Hash — через хеш-таблицу, не требует сортировки. Group — требует отсортированный вход (или Sort перед собой).
• Limit — обрезает поток после первых N строк. В сочетании с Index Scan даёт паттерн «топ-N»: быстро.
• Filter: — пометка в узле, что после чтения применяется ещё один фильтр (то, что не покрылось индексом).
• Index Cond: — пометка в Index Scan: какое условие сужает диапазон в самом индексе.

Разница между Index Cond и Filter важна: то, что в Index Cond — сужает выборку до чтения heap. То, что в Filter — отсеивает строки после чтения. Если после Filter остаётся 1% строк, ты прочитал 99% впустую — стоит подумать о расширении индекса.

Параллельные узлы

В PG 9.6+ появились параллельные планы. В выводе встретишь:

• Gather / Gather Merge — точка, в которой основной процесс собирает результаты от worker-процессов.
• Parallel Seq Scan — несколько worker’ов одновременно сканируют разные куски таблицы.
• Parallel Hash Join — параллельное построение hash-таблицы и обход.

Параллелизм не магический: он включается на больших таблицах (min_parallel_table_scan_size) и не на всех типах запросов. Часто помогает на read-heavy аналитике, редко — на коротких OLTP-запросах. В выводе обращай внимание на Workers Planned: N и Workers Launched: N — это может различаться, если воркеров не хватило.

Buffers: главный фактор реальной скорости

В EXPLAIN (ANALYZE, BUFFERS) появится колонка Buffers: shared hit=A read=B:

• shared hit — страницы, найденные в shared buffer cache (RAM). Очень быстро.
• shared read — страницы, прочитанные с диска. На порядки медленнее.

Если у тебя план с Buffers: shared read=10000 — это 80 МБ с диска. Даже на SSD это десятки миллисекунд. Если та же страница пришла из cache — наносекунды. Поэтому первый запуск запроса часто в 100 раз медленнее повторных — данные просто доехали в RAM.

В выводе обрати внимание на:

• Узлы и их иерархию (отступы).
• Поля cost, actual time, rows.
• Расхождения между estimate и actual.
• Hash Cond, Index Cond, Filter.
• Buffers: shared hit=... — что в кеше, что нет.

Сравнение «до и после» индекса

Самый практичный способ убедиться, что индекс помог — взять EXPLAIN ANALYZE запроса, создать индекс, запустить тот же EXPLAIN ANALYZE. Сравнить actual time.

В реальной работе разница на большой таблице может быть 1000ms vs 5ms — и тогда ты точно видишь, что индекс работает.

Рабочий процесс анализа плана

Когда стоит задача «этот запрос медленный, разберись» — вот примерный алгоритм:

1. Запусти EXPLAIN (ANALYZE, BUFFERS) <query>. Если запрос меняющий — заверни в BEGIN ... ROLLBACK.
2. Посмотри на корневой узел: какое там actual time и rows? Это итоговая стоимость.
3. Спустись по дереву и найди узлы, где actual time особенно большое. Часто это один-два узла из десятка.
4. Для проблемного узла проверь:
   • rows (estimate) vs actual rows — расходятся?
   • Filter: оставляет ли он много или мало? Если много — индекс не помог достаточно.
   • Buffers: read=... — много чтений с диска? Может, кеш холодный или таблица слишком большая.
   • loops=N — если N большое (Nested Loop с большим внешним) — вероятно, плохая стратегия соединения.
5. Сформулируй гипотезу: «возможно, помог бы индекс по A» или «надо запустить ANALYZE» или «нужен другой запрос».
6. Проверь гипотезу: создай индекс, запусти план снова, сравни actual time.

Этот цикл — не наука, а ремесло. С опытом ты будешь сразу видеть узкие места в плане; пока — следуй алгоритму.

Чек-лист

• EXPLAIN показывает план без выполнения. EXPLAIN ANALYZE — с выполнением и реальными числами.
• План — это перевёрнутое дерево, читай снизу вверх по отступам.
• cost=A..B — startup и total стоимость в условных единицах PG. rows=N — оценка.
• В ANALYZE сравнивай rows (estimate) vs actual rows — крупное расхождение значит плохую статистику.
• Узлы сканирования: Seq Scan / Index Scan / Index Only Scan / Bitmap Heap Scan.
• Узлы соединения: Nested Loop (мал. левый + индекс справа), Hash Join (без индекса), Merge Join (отсортированные входы).
• (ANALYZE, BUFFERS) показывает shared hit vs shared read — что из RAM, что с диска.
• Различай Index Cond: (сужает в индексе) и Filter: (фильтрует после чтения).
• Параллельные планы: Gather + Parallel Seq Scan / Parallel Hash Join — для больших таблиц.
• Опции EXPLAIN: ANALYZE, BUFFERS, VERBOSE, FORMAT JSON, SETTINGS — комбинируй по задаче.
• Алгоритм разбора: смотри корень → ищи узел с большим actual time → проверяй rows/Filter/Buffers/loops → формируй гипотезу → проверяй планом.
• Чтение плана — это не наука, а ремесло. С опытом узкие места видны сразу; пока — следуй алгоритму.
• EXPLAIN — главный инструмент перфоманс-разбора. Без него любой разговор об оптимизации — шаманство.
• Сравнивай планы «до и после» индекса — единственный честный способ убедиться, что помогло.