EXPLAIN без модификаторов — это «как Postgres СОБИРАЕТСЯ выполнять запрос». Цифры в нём — оценки на основе статистики, но никакого реального исполнения не происходит. Чтобы понять, что на самом деле сделал executor — нужен EXPLAIN ANALYZE.

С точки зрения механики разница простая: EXPLAIN строит план и печатает его; EXPLAIN ANALYZE строит план, выполняет его и печатает дополнительно собранные runtime-метрики. Запрос реально работает, реально читает данные, реально модифицирует, если это DML. И именно поэтому при EXPLAIN ANALYZE INSERT/UPDATE/DELETE без обёртки BEGIN; ... ROLLBACK; ты получишь изменённую таблицу.

Новые числа в плане

С ANALYZE каждая строка плана получает дополнительный блок:

Seq Scan on orders  (cost=0.00..2350.00 rows=16667 width=33)
                    (actual time=0.018..18.234 rows=16548 loops=1)
  Filter: (status = 'paid'::text)
  Rows Removed by Filter: 83452

Три новых числа: actual time, actual rows, loops. Плюс часто появляются дополнительные строки Rows Removed by Filter, Heap Fetches, Buffers, Memory Usage, Workers. Разберём главные.

actual time = startup..total в миллисекундах

В отличие от cost (безразмерных единиц),

• startup — сколько мс прошло от начала выполнения узла до момента, когда он отдал первую строку родителю.
• total — сколько мс прошло от начала выполнения узла до момента, когда он отдал последнюю строку.

Для streaming-узлов (Seq Scan, Index Scan, Nested Loop с lazy inner) startup близок к 0, total растёт со временем чтения. Для blocking-узлов (Sort, Hash, HashAggregate, Materialize) startup большой — равен почти всему total, потому что узел сначала собирает весь input, потом отдаёт.

В корне плана отдельной строкой выводится Execution Time: N ms — реальное время всего запроса, включая communication с клиентом (но без planning time, который выводится отдельной строкой Planning Time).

actual rows и loops: главная ловушка

actual rows — это среднее количество строк, которое узел отдал родителю за одну итерацию. То есть это не суммарное число — это среднее на loop.

loops — сколько раз узел исполнялся. Для большинства узлов loops=1. Но в двух случаях loops > 1:

1. Nested Loop с lazy inner. Inner-сторона запускается заново для каждой outer-строки. loops = outer.rows.
2. Parallel-планы. Под Gather каждый worker = отдельный исполнитель того же поддерева. loops = число workers + 1 (если leader тоже работал).

Total rows produced = actual_rows × loops.

Это типичная ловушка. Видишь в плане:

->  Index Scan using idx_orders_customer on orders
      (actual time=0.020..0.045 rows=10 loops=10000)

Кажется: «10 строк, 0.045 мс — быстро». На самом деле: 100 000 строк прошло через этот узел, и общее время на нём — 0.045 × 10000 = 450 мс. Это уже не «быстро», это самая дорогая часть запроса.

В диагностике senior-разработчики всегда умножают actual rows и actual time на loops, прежде чем оценивать стоимость узла.

Запустим и сравним

Возьмём medium-датасет и сравним обычный EXPLAIN и EXPLAIN ANALYZE.

В оценке ты увидишь только cost, rows, width. Теперь то же с ANALYZE.

Сравни estimated rows и actual rows на Seq Scan. Если планировщик ожидал 16667 строк, а реально вернулось 8000 — это отклонение в 2 раза. Для одного узла приемлемо, но если бы дальше шёл join, расхождение бы каскадно усиливалось.

Внутри inner-узла будет что-то вроде loops=20: это сколько customers удовлетворили country='RU' AND is_vip. Каждый раз executor забегает в indexes и ищет матчинг orders. actual rows × loops показывает, сколько всего matched orders прошло через узел.

После эксперимента не забудь:

estimated vs actual: диагностика плохих планов

Главная причина плохого плана — расхождение между estimated и actual rows на промежуточном узле. Планировщик выбирал алгоритм join’а, исходя из 100 ожидаемых строк, а реально пришёл миллион — Hash Join, который мог бы быть Nested Loop, теперь съел всю память.

Что считается «нормальным» и «плохим» отклонением:

• < 2x — отлично; статистика точная.
• 2-10x — приемлемо, особенно для сложных предикатов. Не повод чинить.
• 10-100x — плохо; план вероятно неоптимальный. Проверь корреляцию между колонками, рассмотри extended statistics.
• > 100x — катастрофа; почти наверняка статистика устарела или есть скрытая корреляция/перекос.

Источники расхождения:

1. Статистика устарела — давно не было ANALYZE. Решение: VACUUM ANALYZE table;.
2. Перекос (skew) — одно значение колонки занимает 90% строк. Решение: default_statistics_target повысить, чтобы MCV list стал точнее.
3. Корреляция между колонками — Postgres по умолчанию считает колонки независимыми. Если country='RU' коррелирует с language='ru', оценка country=RU AND language=ru будет в 5-10 раз ниже actual. Решение: CREATE STATISTICS ... (dependencies) ON ....
4. Функциональный индекс / выражения — Postgres плохо оценивает WHERE substring(name, 1, 3) = 'RU_'. Решение: extended statistics на выражение.

Конкретные примеры разбора

Возьмём типичный пример и пройдём по нему как senior.

Aggregate  (cost=22500..22501 rows=1 width=8)
           (actual time=850.234..850.235 rows=1 loops=1)
  -> Hash Join  (cost=300..20000 rows=1000 width=12)
                (actual time=15.123..820.456 rows=85000 loops=1)
       Hash Cond: (o.customer_id = c.id)
       -> Seq Scan on orders o
            (cost=0..15000 rows=1000 width=8)
            (actual time=0.020..650.123 rows=85000 loops=1)
            Filter: (status = 'paid')
            Rows Removed by Filter: 415000
       -> Hash  (cost=180..180 rows=10000 width=8)
                (actual time=14.000..14.001 rows=10000 loops=1)
            -> Seq Scan on customers c
                 (cost=0..180 rows=10000 width=8)
                 (actual time=0.005..8.500 rows=10000 loops=1)

Planning Time: 1.234 ms
Execution Time: 850.456 ms

Что мы видим:

1. Корень — Aggregate, отдаёт 1 строку. actual time=850.234..850.235 — startup ≈ total, это нормально для агрегата (нужно всё прочитать).
2. Hash Join ниже: estimated rows=1000 vs actual rows=85000 — отклонение 85x, серьёзная проблема статистики.
3. Seq Scan на orders: estimated=1000, actual=85000. Filter status='paid'. Rows Removed = 415000 (всего таблица 500K). 85K/500K = 17% selectivity, но планировщик думал 0.2%.
4. Seq Scan на customers: estimate сошёлся (10000). Тут всё нормально.

Диагноз: статистика на orders.status устарела или искажена. После ANALYZE orders; estimated rows должен подтянуться. Также если запрос частый — индекс на orders(status) уберёт Seq Scan и Filter.

Это типичный workflow: смотришь корень → большие cost’ы → проверяешь estimate vs actual на каждом узле сверху вниз → находишь узел с самым большим расхождением → лечишь его.

Что значит «slow» в строке плана

Когда ты говоришь «вот этот узел медленный», ты имеешь в виду одно из:

1. Высокий total time на узле (с учётом loops!). Узел занял много миллисекунд.
2. Высокая стоимость на одну строку: time / rows большая. Узел сам по себе быстрый, но возвращает мало.
3. estimated и actual сильно расходятся. Узел медленный потому, что родитель выбрал плохой алгоритм из-за неправильной оценки.

Только первый случай — «правда медленный узел». Второй и третий — симптомы. Senior-разработчик начинает диагностику с поиска самого дорогого узла по total time × loops, потом проверяет estimated/actual на нём и его детях.

В EXPLAIN ANALYZE есть служебный режим (VERBOSE, FORMAT JSON), который даёт всю информацию структурированно — удобно для парсинга в инструментах типа explain.depesz.com или pev2. На больших планах визуализация — лучший способ найти hot spot.

ANALYZE: что добавилось ещё

Кроме actual time/rows/loops, ANALYZE даёт:

• Rows Removed by Filter: N — сколько строк прочитано, но отфильтровано предикатом узла. Большой Rows Removed на Seq Scan — кандидат на индекс.
• Heap Fetches: N — для Index Only Scan: сколько раз пришлось всё-таки заглянуть в heap (visibility map говорит «не all-visible»). Если число большое — пора VACUUM.
• Memory Usage / Memory: N kB — для Hash, Sort, Materialize. Если рядом написано Disk или external merge — work_mem не хватило, ушло на диск.
• Workers Planned / Workers Launched — для parallel-планов. Если Launched < Planned, у Postgres не было свободных слотов max_parallel_workers.

Эти подсказки появляются по контексту. Senior читает все строки под scan-узлами и под blocking-узлами.

Settings, WAL и другие модификаторы

В скобках EXPLAIN можно положить не только ANALYZE и BUFFERS. Полный список (Postgres 17):

• ANALYZE — реально выполнить запрос, собрать runtime-метрики.
• VERBOSE — добавить output list каждого узла, schema-qualified names, per-worker детали.
• COSTS (по умолчанию on) — показывать cost/rows/width. С COSTS off план без стоимостей — для regression-тестов, чтобы сравнивать «структуру» планов без чувствительности к числам.
• SETTINGS — показать non-default GUC-параметры, влияющие на план (enable_*, work_mem, random_page_cost если переопределены).
• WAL (с PG 13) — для DML: сколько WAL-байт записано во время выполнения. Полезно для тюнинга wal_compression, synchronous_commit.
• TIMING (по умолчанию on при ANALYZE) — собирать actual time. Можно отключить (TIMING off), если интересны только rows/loops и нужно минимизировать gettimeofday-overhead.
• SUMMARY — отдельная финальная строка с Planning/Execution time (включена по умолчанию для ANALYZE).
• FORMAT TEXT|JSON|YAML|XML — формат вывода.

Пример полного «production-grade» EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, WAL, FORMAT JSON)
SELECT ...;

В выводе будут абсолютно все метаданные, и JSON парсится автоматически. Это формат, которым обычно делятся, когда просят помощи в анализе плана.

Цена ANALYZE

ANALYZE — это не бесплатная команда. Она:

1. Реально исполняет запрос. Время = реальное время + overhead на gettimeofday в каждом узле.
2. Замедляет узлы с миллионами итераций. Если в плане Nested Loop с inner loops=1M, gettimeofday для каждой итерации — это ~10-100 нс × 2 × 1M = 20-200 мс overhead, который не существует в обычном выполнении. Параметр track_io_timing усугубляет это для буферных метрик.
3. DML реально модифицирует. EXPLAIN ANALYZE DELETE FROM orders; удалит все orders. Обязательно: BEGIN; EXPLAIN ANALYZE DELETE ...; ROLLBACK;.
4. На очень коротких запросах overhead больше пользы. Если запрос работает 50 мкс, EXPLAIN ANALYZE покажет 200 мкс — само измерение медленнее запроса.

Для очень тяжёлых запросов на проде, где нельзя запустить, есть auto_explain extension — он сохраняет план каждого слишком долгого запроса автоматически. К нему вернёмся в последнем уроке модуля.

Сравнение планов: до и после

Один из самых полезных навыков — сравнить два плана и понять, что именно изменилось. Обычно делается:

1. Снимаешь план до оптимизации (например, без индекса): сохраняешь.
2. Применяешь изменение (создаёшь индекс / меняешь predicate / повышаешь work_mem).
3. Снимаешь план после.
4. Сравниваешь:
   • Изменилась ли структура плана? Был Seq Scan — стал Index Scan? Был Hash Join — стал Nested Loop? Изменение алгоритма = большое изменение.
   • Изменился ли total cost корня? Если упал в N раз — планировщик думает, что план быстрее.
   • Изменилось ли execution time? Если cost упал, а time нет — план «выглядит хорошо», но реально медленнее. Проверь Buffers — возможно, на новом плане cache miss больше.

Сравни: cost упал? Execution Time упал? Структура плана изменилась с Seq Scan на Index Scan?

Чек-лист

• EXPLAIN ANALYZE реально исполняет запрос. Для DML — оборачивай в BEGIN; ... ROLLBACK;.
• Новые числа: actual time=startup..total (миллисекунды), actual rows, loops.
• Total rows = actual_rows × loops. Это самая частая ловушка чтения плана.
• Сравнение estimated vs actual — главный диагностический инструмент.
• Расхождение > 10x — повод проверить ANALYZE, статистику, extended statistics.
• Дополнительные строки: Rows Removed by Filter, Heap Fetches, Memory, Workers.
• ANALYZE имеет overhead 1.5-3x на time-критичных запросах. На проде используй auto_explain.