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

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

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

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

PG-E-030 Для диагностики всегда EXPLAIN (ANALYZE, BUFFERS). Без ANALYZE это только оценка; без BUFFERS не видно, прочитан ли блок с диска или из кеша.

⚠️ EXPLAIN ANALYZE выполняет запрос. Для INSERT/UPDATE/DELETE — оборачивать в транзакцию с ROLLBACK:

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

2. Как читать вывод

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  (cost=...) (actual time=...) (rows=10000 loops=1)
  ->  Hash  (cost=...) (actual time=...) (rows=1000 loops=1)
        ->  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 = с диска. Чем больше read, тем больше IO.

PG-E-031 Главное — сравнить rows= (оценка) с actual rows=. Расхождение в 10x+ → статистика устарела (см. Селективность).

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

3. Узлы сканирования таблицы

Seq Scan

Seq Scan on orders  (cost=0.00..18334.00 rows=100000 width=64)
  Filter: (status = 'PAID')
  Rows Removed by Filter: 850000

Полный проход таблицы. Каждая строка проверяется на Filter.

Когда выбирается:

• Нет подходящего индекса.
• Запрос отдаёт большую долю таблицы (>20% обычно).
• Таблица маленькая (планировщик считает, что оверхед индекса не окупится).

Когда плох:

• На больших таблицах с селективным предикатом — Rows Removed by Filter гигантский.

Index Scan

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

Проход по B-tree-индексу + чтение строк из таблицы.

• Index Cond: — условие, использованное для поиска по дереву.
• Filter: — дополнительный фильтр после чтения строк.

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 + Bitmap Heap Scan

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

Двухфазное:

1. Bitmap Index Scan — проходит индекс, строит битмап страниц с подходящими строками.
2. Bitmap Heap Scan — читает найденные страницы по порядку (sequential, не random).

Когда выбирается:

• Условие отдаёт средне-большую долю строк.
• Несколько индексов комбинируются (BitmapOr, BitmapAnd).

PG-E-035 Если Recheck Cond: показывает повторную проверку — это lossy bitmap (битмап вместил только страницы, не сами строки). На больших выборках теряется эффективность.

4. Узлы соединения (Join)

Nested Loop

Nested Loop  (cost=...) (actual time=...) (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)

Для каждой строки внешнего отношения — поиск во внутреннем. Если внутреннее имеет индекс по join-ключу — очень эффективно для маленьких выборок.

Когда хорошо: outer rows × inner cost — мало (маленький outer, индекс на inner).

Когда плохо: outer rows × inner cost — много. Если видите loops=1000000 с actual time=0.5 на каждый — это 500 сек внутри Nested Loop. Лучше Hash Join.

Hash Join

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

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

Когда хорошо: меньшее отношение помещается в work_mem. Быстрее всего на больших соединениях без подходящих индексов.

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

Merge Join

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

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

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

5. Сортировка и группировка

Sort

Sort  (cost=...) (actual time=12.345..56.789 rows=10000 loops=1)
  Sort Key: created_at DESC
  Sort Method: external merge  Disk: 8192kB

PG-E-037 Sort Method: external merge Disk: ...kB — сортировка не уместилась в память, ушла на диск. Увеличить work_mem или вынести сортировку в индекс.

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

• quicksort — в памяти, быстро.
• top-N heapsort — для ORDER BY ... LIMIT N, держит в памяти только N лучших.

Aggregate / GroupAggregate / HashAggregate

HashAggregate
  Group Key: customer_id
  ->  Seq Scan on orders

• Aggregate — простой count(*) / sum() без GROUP BY.
• HashAggregate — GROUP BY через hash-таблицу в памяти. Быстро, не требует сортировки.
• GroupAggregate — GROUP BY после Sort. Используется, когда HashAggregate не помещается в work_mem или нужен порядок.

Limit

Limit  (cost=...) (actual time=...) (rows=20)
  ->  Index Scan Backward on ix_orders_created_at

Останавливает выполнение, когда набралось N строк. Эффективен, если ниже — Index Scan (не нужно сортировать всё).

6. Прочие узлы

Узел	Что делает
Materialize	Кеширует результат подноды в памяти, чтобы не пересчитывать (часто внутри Nested Loop).
Subquery Scan	Обёртка над подзапросом.
CTE Scan	Чтение из CTE (с PG12+ часто inline без CTE Scan).
Append	Объединение результатов нескольких подзапросов (партиционирование, UNION ALL).
Gather / Gather Merge	Параллельный план — собирает результаты от worker-процессов.
Memoize (PG14+)	Кеширует результаты повторяющихся подзапросов внутри Nested Loop.
Result	Единичный результат без сканирования (например, SELECT 1).

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

Gather  (cost=...) (rows=...)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on orders

PostgreSQL может разбить sequential scan / hash join / aggregate на несколько worker-процессов.

PG-E-038 Параллелизм оправдан на больших таблицах. На мелких — оверхед запуска worker-ов больше выигрыша.

Управляется параметрами:

• max_parallel_workers_per_gather (по умолчанию 2).
• parallel_setup_cost / parallel_tuple_cost.
• min_parallel_table_scan_size (по умолчанию 8MB) — таблица меньше → не параллелится.

8. Чек-лист «как читать план»

PG-E-039 Алгоритм:

1. Читать снизу вверх. Дерево плана: листья — самые внутренние операции, корень — финальный результат.
2. Найти узкое место по actual time. Узел с самым большим вкладом (actual time × loops минус время дочерних) — там фокус.
3. Сверить rows= и actual rows=. Расхождение → ANALYZE.
4. Проверить Buffers: read=. Большое read → IO-bound, нужен индекс или больше кеша.
5. Filter vs Index Cond. Если важное условие в Filter — индекс не используется как ключ.
6. Heap Fetches > 0 на Index Only Scan → VACUUM.
7. external merge Disk: или Batches > 1 в Hash Join → увеличить work_mem.
8. Nested Loop с большим loops → проверить, есть ли индекс на inner join-ключе. Если нет — Hash Join будет лучше.
9. Rows Removed by Filter миллионы → нужен индекс, который сузит выборку до фильтра.

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

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

• explain.depesz.com — кладёшь plan, получаешь раскрашенную таблицу с подсветкой проблем.
• explain.dalibo.com (PEV2) — графическое дерево, открываешь в браузере, не отправляя план в чужие сервисы.
• pg_stat_statements — расширение для агрегации статистики по запросам в проде. Показывает топ по total_time, mean_time, calls.

10. Когда EXPLAIN не помогает

EXPLAIN ANALYZE показывает то, что ОДИН запрос делает СЕЙЧАС. Не показывает:

• Локскипы и блокировки — для них pg_locks + pg_stat_activity.
• autovacuum / wraparound — для них pg_stat_user_tables.
• Memory pressure — top / htop / pg_stat_database.
• Replication lag — pg_stat_replication.

PG-E-040 EXPLAIN — про план одного запроса. Для проблем нагрузки и производительности кластера нужны pg_stat_*-views и метрики.

---

Связанные

• Composite-индексы — почему Index Cond vs Filter.
• Селективность — почему оценка строк может быть кривой.
• Типы индексов — какой индекс для какого паттерна сканирования.