15 апреля 2026 г.

EXPLAIN SQL: шпаргалка для аналитика

Зачем аналитику EXPLAIN

На senior-позициях вас спросят: «почему этот запрос медленный?». Правильный ответ — «давайте посмотрим EXPLAIN». Без этого вы можете угадывать, но не доказывать.

EXPLAIN vs EXPLAIN ANALYZE

-- План без выполнения (быстро)
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- План + реальное выполнение + время
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

-- С буферами и timing
EXPLAIN (ANALYZE, BUFFERS, TIMING) SELECT * FROM orders WHERE user_id = 1;

-- В формате JSON
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ...;

ANALYZE действительно выполняет запрос — осторожно с UPDATE/DELETE/INSERT!

Структура плана

Sort  (cost=123.45..125.67 rows=100 width=32) (actual time=5.1..5.8 rows=100 loops=1)
  Sort Key: created_at
  ->  Index Scan using idx_orders_user on orders  (cost=0.42..100.00 rows=100 ...)
        Index Cond: (user_id = 1)

Читается снизу вверх и от внутренних операций к внешним:

  1. Сначала Index Scan — достаёт 100 строк.
  2. Потом Sort — сортирует их по created_at.

Если хочется сразу закрепить тему на практике — открой тренажёр в Telegram. 10 минут в день — и синтаксис в пальцах.

Виды сканов

Seq Scan (Sequential Scan)

Полный перебор таблицы:

Seq Scan on orders  (cost=0..1000 rows=10000)
  Filter: (user_id = 1)

Когда норма: маленькие таблицы, или фильтр выбирает >10-20% строк.

Когда плохо: большая таблица + маленькая доля строк + есть индекс — значит, планировщик его не использует. Проверяйте почему.

Index Scan

Использует индекс:

Index Scan using idx_orders_user on orders  (cost=0.42..10)
  Index Cond: (user_id = 1)

Идёт по B-tree индексу, потом читает сами строки из таблицы.

Index Only Scan

Индекс покрывает все нужные столбцы — в таблицу не ходим:

Index Only Scan using idx_orders_user on orders
  Index Cond: (user_id = 1)
  Heap Fetches: 0

Самый быстрый вариант. Достигается covering index с INCLUDE.

Bitmap Heap Scan

Гибрид: собирает битмап матчей из индекса, потом читает из таблицы блоками.

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

Эффективен, когда индекс даёт много совпадений, но не всю таблицу.

Виды JOIN

Nested Loop

Для каждой строки левой — ищет совпадения в правой:

Nested Loop  (cost=... rows=100)
  ->  Seq Scan on users u
  ->  Index Scan on orders o (user_id = u.user_id)

Быстрый для маленьких левых таблиц + индекса на правой.

Hash Join

Строит hash-таблицу на меньшей стороне, сканит большую:

Hash Join
  Hash Cond: (o.user_id = u.user_id)
  ->  Seq Scan on orders
  ->  Hash
        ->  Seq Scan on users

Хорош для равных по размеру таблиц без индексов.

Merge Join

Обе стороны уже отсортированы, идём «в параллель»:

Merge Join
  Merge Cond: (o.user_id = u.user_id)
  ->  Index Scan on orders (sorted by user_id)
  ->  Index Scan on users (sorted by id)

Редко, но эффективен для больших JOIN-ов с индексами-сортировками.

Cost и rows

(cost=1000.00..1500.00 rows=100 width=32)
  • 1000.00..1500.00 — начальная..финальная стоимость (условные единицы).
  • rows=100 — ожидаемое количество строк.
  • width=32 — средний размер строки в байтах.

На собесе: «estimated rows очень далеки от actual rows — это проблема statistics». Решение: ANALYZE orders; для обновления статистики.

Типовые проблемы и решения

1. Seq Scan на большой таблице

Seq Scan on orders  (rows=10_000_000)
  Filter: (user_id = 1)

Нет индекса или он не используется. Добавьте CREATE INDEX idx_orders_user ON orders(user_id);.

2. Неиспользуемый индекс из-за функции

-- Индекс на email не используется
WHERE LOWER(email) = 'x'

Решение: функциональный индекс:

CREATE INDEX ON users (LOWER(email));

3. Slow Sort

Sort Method: external merge  Disk: 256MB

Сортировка в памяти не поместилась, пошло на диск. Решения:

  • Добавить индекс, соответствующий ORDER BY.
  • Увеличить work_mem (DBA).

4. Misestimated rows

Seq Scan on orders (cost=... rows=100) (actual ... rows=1000000)

Огромная разница → плохие статистики. ANALYZE таблицу.

Чтобы не только читать теорию, но и решать реальные задачи — загляните в бот Карьерника. Там по каждой теме подборка вопросов с разборами.

10 практических ситуаций

1. Запрос тормозит

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

Смотрим: Seq Scan или Index Scan, actual time.

2. JOIN замедлился

Смотрим тип JOIN (Nested / Hash / Merge). Если Nested Loop на больших таблицах — проверяем индексы.

3. GROUP BY медленный

Смотрим, есть ли HashAggregate vs GroupAggregate. Hash — быстрее для маленьких групп.

4. ORDER BY + LIMIT

Проверяем, использует ли index для сортировки (нет Sort в плане).

5. DISTINCT

DISTINCT → Unique (после сортировки) или HashAggregate. Если видим Sort — можно заменить на GROUP BY + индекс.

6. Subquery медленный

Смотрим, материализуется он или inline. Explain покажет CTE Scan или подзапрос как часть плана.

7. Multi-column filter

WHERE user_id = 1 AND status = 'paid'

Composite index (user_id, status) даст Index Cond на обоих. Два отдельных — только один.

8. COUNT(*)

COUNT(*) в PostgreSQL — seq scan. Альтернатива — приближённый count из pg_stats.

9. JOIN 5+ таблиц

Оптимизатор может выбрать неоптимальный порядок. Иногда помогает SET join_collapse_limit = 1 или hint.

10. CTE против subquery

EXPLAIN покажет, материализуется ли CTE. В PostgreSQL 13+ обычно inline.

Как тренироваться

EXPLAIN — скилл, который учится только через реальные запросы. Запустите тяжёлый SELECT, посмотрите план, добавьте индекс, посмотрите ещё раз.

Тренажёр Карьерник содержит блок на оптимизацию: EXPLAIN, индексы, как ускорять запросы.

Совет: на собесе, если спрашивают «как бы вы диагностировали медленный запрос», первые слова — «EXPLAIN ANALYZE». Это сразу +2 балла за практичность.

Читайте также

FAQ

EXPLAIN или EXPLAIN ANALYZE?

EXPLAIN — оценка плана, не выполняет запрос. ANALYZE — выполняет и показывает реальное время. Для SELECT можно ANALYZE. Для UPDATE/DELETE/INSERT — ОПАСНО, он выполнит запрос. Используйте EXPLAIN (ANALYZE FALSE) для них.

Как понять, что индекс нужен?

Seq Scan на большой таблице с узким фильтром + actual rows << total rows. Если ваш запрос читает 100 строк, а Seq Scan перебирает миллион — индекс ускорит в 10 000 раз.

Что такое cost в EXPLAIN?

Условные единицы планировщика. Учитывает seq page reads, random page reads, CPU. Абсолютное значение не важно — важно сравнение разных планов одного запроса.

Почему планировщик не использует мой индекс?

Частые причины: (1) функция поверх столбца — WHERE LOWER(col), (2) implicit cast, (3) старая статистика — ANALYZE поможет, (4) маленькая таблица — Seq Scan быстрее.

Тренироваться в Telegram