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

EXPLAIN — главный инструмент для понимания того, что происходит с твоим SQL-запросом внутри базы. Без него оптимизация — это гадание на кофейной гуще. Разберём как читать план выполнения на реальных примерах.

EXPLAIN vs EXPLAIN ANALYZE

-- EXPLAIN — только план, запрос НЕ выполняется
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- EXPLAIN ANALYZE — выполняет запрос и показывает реальное время
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- EXPLAIN (ANALYZE, BUFFERS) — ещё и статистика кэша
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42;

Осторожно: EXPLAIN ANALYZE реально выполняет запрос. Для UPDATE/DELETE оборачивай в транзакцию и делай ROLLBACK.

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'cancelled' WHERE user_id = 42;
ROLLBACK;

Читаем план: основы

Seq Scan on orders  (cost=0.00..1250.00 rows=5 width=64)
                                                (actual time=0.042..12.8 rows=3 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 49997
Planning Time: 0.8 ms
Execution Time: 12.9 ms

Разберём каждую часть:

Типы сканирования

Seq Scan — последовательное сканирование

Seq Scan on orders  (cost=0.00..1250.00 rows=50000 width=64)

База читает всю таблицу сверху вниз. Нормально для маленьких таблиц или когда нужно вернуть >20% строк. На большой таблице с точечным запросом — сигнал о нужном индексе.

Index Scan — поиск по индексу

Index Scan using idx_orders_user_id on orders
  (cost=0.43..8.45 rows=3 width=64)
  Index Cond: (user_id = 42)

PostgreSQL использует B-tree индекс для поиска строк. Эффективно для точечных запросов с высокой селективностью.

Index Only Scan — только индекс, без таблицы

Index Only Scan using idx_orders_covering on orders
  (cost=0.43..4.45 rows=3 width=16)
  Heap Fetches: 0

Все нужные данные есть в индексе — таблица не читается. Heap Fetches: 0 — идеально. Это самый быстрый вариант.

Bitmap Heap Scan

Bitmap Heap Scan on orders
  Recheck Cond: (user_id = 42)
  ->  Bitmap Index Scan on idx_orders_user_id
        Index Cond: (user_id = 42)

Промежуточный вариант: сначала строится битовая маска подходящих страниц (Bitmap Index Scan), потом читаются только они (Bitmap Heap Scan). Используется когда строк много, но не все.

Операции соединения

Hash Join

Hash Join  (cost=1500.00..3200.00 rows=5000 width=128)
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders
  ->  Hash
        ->  Seq Scan on users

PostgreSQL строит хэш-таблицу из меньшей таблицы, потом проходит по большей. Хорош для больших таблиц без индекса на колонке JOIN.

Nested Loop

Nested Loop  (cost=0.43..24.52 rows=3 width=128)
  ->  Seq Scan on users
  ->  Index Scan using idx_orders_user_id on orders
        Index Cond: (o.user_id = u.id)

Для каждой строки внешней таблицы ищет совпадения во внутренней через индекс. Лучший вариант когда внешняя таблица маленькая + есть индекс на внутренней.

Merge Join

Обе таблицы отсортированы по ключу JOIN — PostgreSQL идёт по ним параллельно. Редко встречается, хорош для больших уже отсортированных наборов.

Практический пример: диагностика проблемы

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS orders_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2026-01-01'
GROUP BY u.name
ORDER BY orders_count DESC;

Плохой план:

Sort  (actual time=850.3..850.4 rows=100 loops=1)
  ->  HashAggregate  (actual time=849.9..850.1 rows=100)
        ->  Hash Join  (actual time=320.5..845.2 rows=15000)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders  (actual time=0.1..400.1 rows=500000)
                    Filter: (created_at >= '2026-01-01')
                    Rows Removed by Filter: 450000  ← проблема!
              ->  Hash  (actual time=10.1..10.1 rows=10000)
                    ->  Seq Scan on users
Execution Time: 852.1 ms

Видим: Seq Scan on orders с фильтрацией 450 000 строк. Нужен индекс на created_at.

CREATE INDEX idx_orders_created_at ON orders(created_at);

Хороший план после индекса:

Sort  (actual time=12.3..12.4 rows=100 loops=1)
  ->  HashAggregate  (actual time=11.9..12.1 rows=100)
        ->  Hash Join  (actual time=3.5..10.8 rows=50000)
              ->  Index Scan using idx_orders_created_at on orders
                    Index Cond: (created_at >= '2026-01-01')  ← намного лучше
              ->  Hash
                    ->  Seq Scan on users
Execution Time: 13.2 ms  ← было 852 мс, стало 13 мс

Расхождение оценок и реальности

rows=5 (оценка) vs actual rows=50000 (реально)

Если оценка сильно расходится с реальностью — устаревшая статистика. Лечится:

-- Обновить статистику по таблице
ANALYZE orders;

-- Или для конкретной колонки
ANALYZE orders (user_id, created_at);

Полезные флаги EXPLAIN

-- Показать использование кэша (shared hit = из кэша, read = с диска)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42;

-- Вывод в JSON для дальнейшего разбора
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT * FROM orders WHERE user_id = 42;

-- Подробный формат с отступами
EXPLAIN (ANALYZE, FORMAT TEXT, VERBOSE)
SELECT * FROM orders WHERE user_id = 42;

Быстрый чек-лист при медленном запросе

1. EXPLAIN ANALYZE — смотри на Seq Scan с большим Rows Removed
2. Проверь есть ли индексы на колонки в WHERE, JOIN ON, ORDER BY
3. Функции над колонками в WHERE убивают индекс — убери их
4. Большое расхождение оценки и реальности — запусти ANALYZE
5. Hash Join на огромных таблицах — проверь есть ли индекс на ключе JOIN

Хочешь попробовать прямо сейчас? В нашем инструменте EXPLAIN можно запустить любой запрос и увидеть план выполнения — без установки PostgreSQL.