EXPLAIN ANALYZE — типы узлов и как читать план

Опирается на правила: PG-E-030 … PG-E-039 из PostgreSQL Style Guide → раздел EXPLAIN ANALYZE.

Важно знать

EXPLAIN (ANALYZE, BUFFERS) — стандарт. Без BUFFERS не видно IO.

EXPLAIN ANALYZE выполняет запрос — для INSERT/UPDATE оборачивать BEGIN; ... ROLLBACK;.

Реальное время узла = actual time × loops.

Расхождение rows/actual rows 10x+ — ANALYZE.

Filter: vs Index Cond: — если важное условие в Filter, индекс не используется как ключ.

Heap Fetches > 0 на Index Only Scan — VACUUM.

Batches > 1 в Hash Join — увеличить work_mem.

external merge Disk: в Sort — увеличить work_mem или вынести в индекс.

Читать снизу вверх — листья дерева плана внутренние операции.

EXPLAIN ANALYZE — главный инструмент диагностики. UCP формулирует справочник по узлам и метрикам.

Базовый синтаксис

PG-E-030:

EXPLAIN ANALYZE SELECT ...;                       -- реально выполнит + actual time/rows
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;             -- + сколько блоков из кеша/диска
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;    -- + полный output-список колонок
EXPLAIN (FORMAT JSON) SELECT ...;                  -- структурированно для tooling

⚠️ EXPLAIN ANALYZE выполняет запрос. Для DML:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'X' WHERE id = 123;
ROLLBACK;

Как читать

Hash Join  (cost=12.34..567.89 rows=1000 width=64) (actual time=1.234..56.789 rows=987 loops=1)
  Hash Cond: (a.id = b.a_id)
  Buffers: shared hit=1234 read=56
  ->  Seq Scan on a
  ->  Hash
        ->  Seq Scan on b

cost=START..TOTAL — оценка в условных единицах (не миллисекунды).
rows=N — оценка количества возвращаемых строк.
width=B — размер строки в байтах.
actual time=START..TOTAL — реальное в миллисекундах.
actual rows=N loops=M — реальное число строк × число запусков узла.
Buffers: shared hit=X read=Y — hit= кеш, read= диск.

PG-E-031: главное — rows= (оценка) vs actual rows=. Расхождение 10x+ → ANALYZE.

PG-E-032: реальное время узла = actual time × loops. loops=10000 × actual time=0.5..1.2 = 12 секунд внутри Nested Loop.

Узлы сканирования

Seq Scan

Seq Scan on orders
  Filter: (status = 'PAID')
  Rows Removed by Filter: 850000

Полный проход таблицы.

Когда: нет индекса, > 20% строк по условию, маленькая таблица.

Index Scan

Index Scan using ix_orders_status on orders
  Index Cond: (status = 'PAID')

Проход по B-tree + чтение строк.

PG-E-033: если условие в Filter:, а не в Index Cond: — индекс по этому полю не используется как ключ.

Index Only Scan

Index Only Scan using ix_orders_status_created on orders
  Index Cond: (status = 'PAID')
  Heap Fetches: 12

Все колонки есть в индексе → таблица не читается.

PG-E-034: Heap Fetches > 0 — устаревший visibility map. Помогает VACUUM.

Bitmap Index Scan + Heap Scan

Bitmap Heap Scan on orders
  Recheck Cond: (status = 'PAID')
  ->  Bitmap Index Scan on ix_orders_status

Двухфазное: проходит индекс, строит битмап страниц, читает по порядку (sequential, не random).

Когда: средне-большая доля строк, несколько индексов комбинируются.

PG-E-035: Recheck Cond: показывает повторную проверку = lossy bitmap (вместил только страницы, не строки).

Join

Nested Loop

Nested Loop  (rows=100 loops=1)
  ->  Index Scan on a  (rows=100)
  ->  Index Scan on b  (rows=1 loops=100)
        Index Cond: (b.a_id = a.id)

Для каждой строки внешнего — поиск во внутреннем.

Хорошо: маленький outer × индекс на inner.
Плохо: loops=1000000 × actual time=0.5 = 500 секунд.

Hash Join

Hash Join
  Hash Cond: (a.id = b.a_id)
  ->  Seq Scan on a
  ->  Hash
        ->  Seq Scan on b

Меньшее в hash-таблицу в памяти, потом lookup для большего.

PG-E-036: Buckets: ... Batches: > 1 — hash не уместился в work_mem. Увеличить work_mem для сессии.

Merge Join

Merge Join
  Merge Cond: (a.id = b.a_id)
  ->  Sort ...
  ->  Index Scan on ix_b_a_id

Оба отсортированы по join-ключу, мерджатся как зипкой.

Когда: очень большие отношения, оба с индексами / отсортированы.

Sort, Aggregate

Sort

Sort
  Sort Key: created_at DESC
  Sort Method: external merge  Disk: 8192kB

PG-E-037: external merge Disk: — сортировка на диске. Увеличить work_mem или вынести в индекс.

Альтернативы:

quicksort — в памяти, быстро.
top-N heapsort — для ORDER BY ... LIMIT N.

HashAggregate vs GroupAggregate

Aggregate — простой count(*)/sum().
HashAggregate — GROUP BY через hash в памяти. Быстро.
GroupAggregate — GROUP BY после Sort. Когда HashAggregate не помещается в work_mem.

Параллельный план

PG-E-038:

Gather
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on orders

Параметры:

max_parallel_workers_per_gather (default 2).
parallel_setup_cost, parallel_tuple_cost.
min_parallel_table_scan_size (default 8MB) — таблица меньше → не параллелится.

Чек-лист как читать план

PG-E-039:

Снизу вверх. Листья — внутренние операции, корень — финал.
Узкое место по actual time — узел с большим вкладом за минусом дочерних.
rows= vs actual rows= — расхождение 10x+ → ANALYZE.
Buffers: read= большое → IO-bound, нужен индекс или больше кеша.
Filter: vs Index Cond: — важное условие в Filter = индекс не работает.
Heap Fetches > 0 на Index Only Scan → VACUUM.
external merge Disk: или Batches > 1 → work_mem.
Nested Loop с большим loops → проверить индекс на inner join-ключе.
Rows Removed by Filter миллионы → нужен индекс на фильтре.

Инструменты визуализации

Для сложных планов (5+ уровней):

explain.depesz.com — раскрашенная таблица с подсветкой проблем.
explain.dalibo.com (PEV2) — графическое дерево, работает в браузере.
pg_stat_statements — расширение для агрегации в проде.

Что запрещено

Антипаттерн	Правило	Что взамен
`EXPLAIN` без `ANALYZE` для диагностики	`PG-E-030`	`(ANALYZE, BUFFERS)`
`EXPLAIN ANALYZE UPDATE/INSERT` без транзакции	`PG-E-030`	`BEGIN ... ROLLBACK`
Игнорировать `loops` × `actual time`	`PG-E-032`	реальное = умножение
Доверять `Index Only Scan` без проверки `Filter`	`PG-E-033`	проверять `Index Cond`
`Heap Fetches > 0` оставлять	`PG-E-034`	`VACUUM`
`Batches > 1` оставлять	`PG-E-036`	`work_mem`
`external merge Disk:` игнорировать	`PG-E-037`	`work_mem` или индекс с сортировкой
Параллелизм на мелких таблицах	`PG-E-038`	`min_parallel_table_scan_size`

Куда дальше

PG → EXPLAIN ANALYZE — нормативные формулировки.
Типы индексов — что должно появиться в плане.
Composite-индексы — Index Only Scan детали.
Селективность — почему rows != actual rows.
VACUUM и autovacuum — Heap Fetches.
Мониторинг и slow queries — pg_stat_statements.