В предыдущих модулях мы разбирали отдельные кирпичи: страницы, индексы, планировщик, статистику, join-алгоритмы. Этот модуль склеивает их в одну картинку — план запроса, которую Postgres показывает командой EXPLAIN. Уметь читать план — то, что отделяет middle-разработчика от senior. Без этого навыка любая оптимизация — гадание на кофейной гуще.

Начнём с самого простого: что вообще выводит EXPLAIN.

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

Когда ты говоришь EXPLAIN SELECT ..., Postgres проходит pipeline parser → rewriter → planner и выдаёт результат планировщика — структуру Plan, представляющую дерево операторов. Каждый узел — один из ~40 типов узлов исполнителя: Seq Scan, Index Scan, Hash Join, Sort, Aggregate, Gather, Limit и так далее.

EXPLAIN рендерит это дерево в текст, где отступ = глубина. Корень — самый верхний узел (он же конечный consumer строк, обычно отдаёт результат клиенту). Листья — узлы scan, читающие

В текстовом представлении это выглядит так:

Aggregate
  -> Hash Join
        Hash Cond: (o.customer_id = c.id)
        -> Seq Scan on orders o
        -> Hash
              -> Seq Scan on customers c

Чем больше отступ — тем глубже узел. У каждого узла может быть 0, 1 или 2 ребёнка. У Seq Scan — 0 (лист). У Sort, Aggregate, Limit — 1. У join-узлов (Hash Join, Merge Join, Nested Loop) — 2: outer (внешний) и inner (внутренний). Outer всегда стоит первым в EXPLAIN.

Bottom-up или top-down

Два способа читать план.

Top-down — для общего понимания: «что вообще делает запрос». Смотришь корень: Aggregate (count) — значит, считаем количество. Дальше Hash Join — джойним. Внутри scan’ы — какие таблицы. Полезно, когда план новый, и ты ещё не понял структуру.

Bottom-up — для разбора стоимости и поиска проблемы. Идёшь от листьев. На каждом узле смотришь: сколько строк он отдаёт родителю? Какая стоимость? Если в листе Seq Scan на таблице в 1M строк — это первый кандидат на оптимизацию. Если в родителе Hash Join получает 1M строк, и build-side hash не помещается в work_mem — батчинг на диск.

В реальной практике senior-разработчики читают bottom-up, потому что план обычно ломается в листьях: плохой scan, отсутствующий индекс, large estimate vs actual. Чем глубже узел — тем больше его влияние на финальное время.

Cost, rows, width — три числа в скобках

Каждая строка плана заканчивается блоком (cost=X..Y rows=N width=W). Это оценки планировщика, не реальные числа (для реальных нужен EXPLAIN ANALYZE, следующий урок).

Разберём по частям.

cost=startup..total — две оценки в абстрактных единицах.

rows=N — оценка количества строк, которые узел отдаст родителю. Берётся из

width=W — средняя ширина одной строки в байтах. Сумма средних размеров колонок, которые узел вытаскивает наверх. width=84 × rows=10000 = ~820 KiB — оценка размера output relation. Это используется для решения «помещаемся ли в work_mem».

Пробуем на живой таблице

Берём medium-датасет (100K orders) и смотрим план простого фильтра.

В выводе ты увидишь что-то вроде:

Seq Scan on orders  (cost=0.00..2350.00 rows=16667 width=33)
  Filter: (status = 'paid'::text)

Это лист — один узел, нет детей. Postgres оценил: при равномерном распределении 6 статусов и 100K строк на каждый придётся ~16667 строк. Стоимость — 0.00..2350.00: 0 на startup (Seq Scan отдаёт первую же строку сразу), 2350 на total (~1350 страниц × seq_page_cost + ~100K × cpu_tuple_cost).

Теперь добавим join.

Структура примерно такая:

Hash Join  (cost=300..3500 rows=16667 width=42)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (cost=0..2350 rows=16667 width=12)
        Filter: (status = 'paid')
  ->  Hash  (cost=180..180 rows=10000 width=34)
        ->  Seq Scan on customers c  (cost=0..180 rows=10000 width=34)

Видишь: outer scan (orders, фильтр paid) отдаёт ~16K строк наверх, inner side (customers) полностью читается в hash table. Hash Join потребляет обе стороны. Сверху — корень с rows=16667: это финальный output.

Обрати внимание: cost корня — не сумма cost’ов детей. У join’а добавляется собственный overhead на probe.

Узлы scan, join, aggregate, sort

Чтобы читать планы, надо знать основные «фигуры». Минимальный набор:

Scan-узлы (листья):

• Seq Scan — последовательное чтение heap, читает все страницы подряд.
• Index Scan — переход в индекс, потом random read’ом за каждой строкой в heap.
• Index Only Scan — только индекс; heap не читается, если visibility map говорит, что страница all-visible.
• Bitmap Index Scan + Bitmap Heap Scan — двухфазный: сначала бит-карта
  bitmap
  , потом по нему — sequential read страниц.

Join-узлы (2 ребёнка):

• Nested Loop — для каждой строки outer — поиск во inner. Хорош, когда outer мал и есть индекс на inner.
• Hash Join — inner весь в hash table, по outer пробегаем и пробим. Хорош, когда inner влезает в work_mem.
• Merge Join — обе стороны отсортированы, сливаем как мерж. Хорош на больших отсортированных наборах.

Промежуточные узлы (1 ребёнок):

• Sort — сортирует input. startup cost большой.
• Aggregate — count/sum/avg. Если без GROUP BY — отдаёт 1 строку, startup = весь input.
• HashAggregate — GROUP BY через hash table.
• Limit — обрывает после N строк. На pipeline’ах с lazy evaluation позволяет планировщику не читать всё.
• Materialize — буферизует input в память для повторного чтения (обычно для Nested Loop с маленьким inner).
• Hash — служебный узел: build hash table для Hash Join.

Parallel-узлы: Gather, Gather Merge, Parallel Seq Scan — отдельный урок (04).

Loops и actual rows: микропредвещание

Пока что мы смотрели только EXPLAIN без ANALYZE. С ANALYZE появляется новое число — loops. Это число раз, которое узел был исполнен.

• Для большинства узлов loops=1.
• Для inner-стороны Nested Loop — loops = outer rows: inner запускается заново для каждой outer-строки.
• Для parallel-узлов loops = workers + 1 (включая leader, если он тоже работал).

Из-за этого actual rows в строке EXPLAIN ANALYZE — это среднее на одну итерацию, не суммарно. Чтобы получить total rows produced на узле, надо умножать actual rows × loops. Это типичная ловушка — детально разберём в следующем уроке.

Несколько распространённых деревьев плана

Чтобы привыкнуть, посмотри на типичные «формы» дерева, которые встречаются регулярно.

Простой scan + filter:

Seq Scan on t  (cost=0..100 rows=50 width=40)
  Filter: (col > 100)

Один узел, лист, нет потомков. Никаких сюрпризов.

Index range scan:

Index Scan using idx_t_col on t  (cost=0.29..15.50 rows=50 width=40)
  Index Cond: (col >= 100 AND col <= 200)

Тоже лист. Index Cond — условие, по которому идёт поиск в B-tree.

Простой aggregate:

Aggregate  (cost=125..125 rows=1 width=8)
  -> Seq Scan on t  (cost=0..100 rows=10000 width=4)

Два уровня: Aggregate потребляет все строки, отдаёт одну (count).

Join двух таблиц через Hash Join:

Hash Join  (cost=180..3500 rows=1000 width=80)
  Hash Cond: (o.cust_id = c.id)
  -> Seq Scan on orders o (...)
  -> Hash (...)
        -> Seq Scan on customers c (...)

Три уровня. Outer scan (orders) — первое поддерево, inner (customers через Hash) — второе.

Three-way join:

Hash Join (a, b+c)
  -> Seq Scan on a
  -> Hash
        -> Hash Join (b, c)
              -> Seq Scan on b
              -> Hash
                    -> Seq Scan on c

Вложенные join’ы. Обычно planner делает «левое дерево» (left-deep): новая таблица всегда добавляется как outer следующего join. Но planner может сделать и «правое» или «bushy» дерево, если cost-оценка лучше.

Sort + Limit:

Limit  (cost=120..120 rows=10 width=40)
  -> Sort
        Sort Key: col
        -> Seq Scan on t

Здесь planner понимает: LIMIT 10 над Sort → Top-N heapsort. Sort собирает только топ-10, не сортирует всё. Это видно по строке Sort Method: top-N heapsort в ANALYZE.

Parallel-форма (см. урок 04):

Finalize Aggregate
  -> Gather
        Workers Planned: 2
        -> Partial Aggregate
              -> Parallel Seq Scan

Двухфазная: workers считают частично, Gather собирает, leader финализирует.

Как читать план: алгоритм

Минимальный workflow для разбора нового плана:

1. Top-level look: посмотри корень. Что отдаётся клиенту? Aggregate? Limit? Hash Join?
2. Найди самые большие cost’ы. Где основная стоимость — в одном scan’е, в join’е, в sort’е?
3. Спустись в листья. На каких таблицах какие scan-операторы? Любой Seq Scan на большой таблице — кандидат на индекс.
4. Проверь rows estimates. Что планировщик ожидает на выходе каждого узла? Сходится ли это с твоим представлением о данных?
5. С ANALYZE — проверь actual vs estimated. Любое отклонение в 10+ раз — красный флаг.

В этом модуле — пять уроков. Дальше мы добавим ANALYZE (реальные числа), BUFFERS (cache hits), посмотрим на parallel-планы и разберём ловушки. Но фундамент — структура дерева и три числа в скобках — уже у тебя.

Где cost берётся из

Числа cost — это не магия. Каждый узел в Postgres имеет функцию cost_* (например, cost_seqscan, cost_hashjoin), которая складывает:

• I/O компонент: pages × seq_page_cost (для seq) или pages × random_page_cost (для random).
• CPU компонент: tuples × cpu_tuple_cost + tuples × cpu_operator_cost × ops_per_tuple.

Цифры по умолчанию: seq_page_cost=1.0, random_page_cost=4.0, cpu_tuple_cost=0.01, cpu_operator_cost=0.0025. На SSD random_page_cost обычно ставят 1.1, иначе планировщик предпочитает Seq Scan там, где Index Scan был бы быстрее. Это самая распространённая настройка под современное железо.

Стоимость scan ровно pages × seq_page_cost + tuples × cpu_tuple_cost объясняет, почему в EXPLAIN большой таблицы видно округлое число вроде cost=0..14730: 13000 страниц × 1.0 + 100000 строк × 0.01 ≈ 14000. Понимать формулу важно при тюнинге random_page_cost для SSD.

Чтение пунктов под узлом

Кроме строки с cost у узла часто есть подстроки, которые тоже несут информацию. Самые важные:

• Index Cond: (...) — условие, по которому фактически идёт поиск в индексе. Только matching строки берутся из heap.
• Filter: (...) — postscan-фильтр, применяемый после извлечения строки. Если рядом есть Rows Removed by Filter, видно, насколько узел работал впустую.
• Sort Key: (...) — по какому ключу sort’ит узел.
• Hash Cond: (...) — условие join’а для Hash Join.
• Merge Cond: (...) — условие для Merge Join.
• Join Filter: (...) — дополнительное условие, применяемое уже после основного матчинга join.
• One-Time Filter — predicate, который вычисляется один раз, не на каждую строку (например, для CASE при constant condition).

Особое внимание на Filter под Index Scan и на One-Time Filter. Первый — обычно симптом неполного индекса. Второй — отлично, означает, что Postgres вычислил, что весь узел можно пропустить (rows=0 после ANALYZE).

Здесь дерево обычно такое: HashAggregate (или GroupAggregate с Sort внутри) → Hash Join → outer = Seq Scan orders, inner = Hash → Seq Scan customers с Filter is_vip. Прочитай его сначала top-down (что делает запрос), потом bottom-up (где основная стоимость).

Формат вывода: TEXT, JSON, YAML

EXPLAIN поддерживает разные форматы:

EXPLAIN (FORMAT JSON) SELECT ...;
EXPLAIN (FORMAT YAML) SELECT ...;
EXPLAIN (FORMAT XML)  SELECT ...;

TEXT — для чтения человеком. JSON/YAML/XML — для парсинга инструментами и сохранения. Сервисы вроде explain.depesz.com принимают TEXT, pev2 — JSON.

JSON-форма особенно удобна, когда план большой (десятки узлов, партиционированная таблица) — там визуализаторы рисуют дерево с подсветкой узких мест, и проще найти самый дорогой узел.

Чек-лист

• План — это дерево узлов; отступ в EXPLAIN = глубина.
• У узлов 0/1/2 ребёнка: scan = лист, sort/agg = 1 child, join = 2 children (outer, inner).
• В скобках: cost=startup..total rows=N width=W. cost — безразмерные единицы планировщика.
• Bottom-up — для разбора стоимости; top-down — для понимания структуры.
• Главные классы узлов: scan (Seq/Index/Bitmap), join (Nested Loop/Hash/Merge), promezhutochnye (Sort, Aggregate, Limit).
• loops появляется только в EXPLAIN ANALYZE — это число итераций узла; actual rows × loops = total rows produced.
• Числа cost детерминированы и считаются из seq_page_cost, random_page_cost, cpu_*_cost плюс статистики из pg_statistic.