EXPLAIN ANALYZE в PostgreSQL: читаем план запроса как профи

14 февраля 2026 г. 16K

Запрос тормозит, но вы не знаете почему. Добавить индекс наугад? Переписать JOIN? Всё это гадание, пока вы не посмотрели план выполнения. EXPLAIN ANALYZE — рентген для SQL: показывает, что планировщик решил делать и сколько это реально заняло.

EXPLAIN vs EXPLAIN ANALYZE — в чём разница

EXPLAIN показывает план без выполнения запроса. Вы видите оценки (estimated rows, cost), но не реальные цифры.

EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- Index Scan using idx_orders_user_id on orders
--   (cost=0.43..8.45 rows=5 width=120)

EXPLAIN ANALYZE реально выполняет запрос и добавляет фактические метрики: actual time, actual rows, loops.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;
-- Index Scan using idx_orders_user_id on orders
--   (cost=0.43..8.45 rows=5 width=120)
--   (actual time=0.023..0.031 rows=7 loops=1)
-- Planning Time: 0.089 ms
-- Execution Time: 0.052 ms

Важно: EXPLAIN ANALYZE выполняет запрос по-настоящему. Для DELETE/UPDATE оборачивайте в транзакцию с ROLLBACK.

Читаем узлы: Seq Scan, Index Scan, Bitmap Heap Scan

План — дерево узлов. Основные типы сканирования:

Seq Scan — последовательное чтение всей таблицы. Нормально для маленьких таблиц или когда нужно вернуть >5-10% строк.

Index Scan — чтение через индекс с обращением к таблице за каждой строкой. Эффективно при малой селективности.

Bitmap Heap Scan — гибрид. Индекс строит битовую карту подходящих страниц, потом таблица читается порциями. Используется когда строк слишком много для Index Scan, но мало для Seq Scan.

-- Bitmap Heap Scan on orders
--   Recheck Cond: (created_at > '2026-01-01')
--   -> Bitmap Index Scan on idx_orders_created_at
--        (actual time=12.5..12.5 rows=150000 loops=1)

Index Only Scan — лучший случай. Все данные есть в индексе, обращение к таблице не нужно.

Nested Loop vs Hash Join vs Merge Join

Nested Loop — для каждой строки внешней таблицы ищет совпадения во внутренней. Быстр при малом количестве строк и наличии индекса.

Hash Join — строит хеш-таблицу из меньшей таблицы, проходит по большей. Эффективен для больших соединений без индексов.

Merge Join — требует обе стороны отсортированными. Быстр, если данные уже отсортированы по индексу.

-- Hash Join  (actual time=45.2..312.8 rows=500000 loops=1)
--   Hash Cond: (orders.user_id = users.id)
--   -> Seq Scan on orders  (actual time=0.01..89.3 rows=5000000)
--   -> Hash  (actual time=44.1..44.1 rows=100000)
--        Buckets: 131072  Memory Usage: 6542kB

Если видите Hash Join с огромной таблицей и temp written — увеличьте work_mem.

Buffers, I/O timing — скрытые метрики

SET track_io_timing = on;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;

-- Index Scan using idx_orders_user_id on orders
--   Buffers: shared hit=5 read=2
--   I/O Timings: shared read=0.215

Что означают метрики:

shared hit — страницы из кеша (shared_buffers). Быстро.
shared read — страницы с диска. Медленно.
temp read/written — временные файлы. Сигнал, что work_mem мал.

Реальный кейс: ускоряем запрос в 50 раз

Задача: последние 10 заказов пользователя со статусом «delivered».

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
WHERE user_id = 1234 AND status = 'delivered'
ORDER BY created_at DESC LIMIT 10;

-- Bitmap Heap Scan on orders
--   Recheck Cond: (user_id = 1234)
--   Filter: (status = 'delivered')
--   Rows Removed by Filter: 4820
--   Buffers: shared hit=312 read=4501
-- Execution Time: 188.1 ms

Проблема: индекс по user_id находит все 4832 заказа, потом фильтрует по статусу и сортирует. Создаём составной индекс:

CREATE INDEX idx_orders_user_status_date
ON orders (user_id, status, created_at DESC);

-- Повторяем:
-- Index Scan using idx_orders_user_status_date on orders
--   Index Cond: (user_id = 1234 AND status = 'delivered')
--   Buffers: shared hit=4
-- Execution Time: 0.09 ms

С 188 мс до 0.09 мс — ускорение в 2000 раз. Индекс покрывает WHERE и ORDER BY, PostgreSQL берёт первые 10 строк без сортировки.

Подводные камни

rows=1 при actual rows=100000 — устаревшая статистика. Выполните ANALYZE tablename.
loops > 1 — actual time показан за один loop. Умножайте на loops для реальной картины.
Planning Time большой — слишком много индексов или партиций.
JIT на коротких запросах — может замедлять. Отключайте: SET jit = off.

Вывод

EXPLAIN ANALYZE — главный инструмент оптимизации в PostgreSQL. Алгоритм: запустите с BUFFERS, найдите узел с наибольшим actual time, поймите причину (Seq Scan вместо Index Scan, Rows Removed by Filter, temp файлы), создайте подходящий индекс, проверьте снова. Не гадайте — измеряйте.