re:re:actable
Урок 034 минуты чтения

Что такое EXPLAIN

На этой странице
Кратко

EXPLAIN показывает план выполнения, который выбрал планировщик: какие узлы (Seq Scan, Index Scan, Hash Join, Sort…) будут выполнены, в каком порядке и с какой оценкой стоимости и числа строк. EXPLAIN ANALYZE реально выполняет запрос и добавляет фактическое время и число строк для каждого узла, а с BUFFERS — сколько страниц прочитано из кеша и с диска. Главное при чтении плана: план читается изнутри наружу; сравнивайте rows (оценка) и actual rows — большое расхождение означает устаревшую статистику или неудачное условие; ищите самые дорогие узлы, Seq Scan по большим таблицам при выборке немногих строк, Sort с external merge Disk, Nested Loop с большим числом loops. EXPLAIN ANALYZE для UPDATE/DELETE выполняют внутри BEGIN … ROLLBACK.

Вопрос

Что такое EXPLAIN и как читать план запроса?

Варианты

EXPLAIN SELECT ...;                         -- только план и оценки
EXPLAIN ANALYZE SELECT ...;                 -- выполнить и показать факт
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;      -- + чтение страниц (в PG 18 BUFFERS включён в ANALYZE по умолчанию)
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ...;  -- для визуализаторов
 
BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE created_at < '2020-01-01';
ROLLBACK;                                   -- изменения не сохранятся

Пример плана

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.email, count(*)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= now() - interval '7 days'
GROUP BY u.email;
HashAggregate  (cost=1520.4..1530.4 rows=1000 width=40) (actual time=12.1..12.6 rows=850 loops=1)
  Group Key: u.email
  ->  Hash Join  (cost=320.0..1470.4 rows=10000 width=32) (actual time=2.1..10.3 rows=9620 loops=1)
        Hash Cond: (o.user_id = u.id)
        ->  Index Scan using orders_created_at_idx on orders o  (... rows=10000) (actual ... rows=9620 loops=1)
              Index Cond: (created_at >= (now() - '7 days'::interval))
              Buffers: shared hit=210
        ->  Hash  (... rows=10000)
              ->  Seq Scan on users u  (... rows=10000) (actual ... rows=10000 loops=1)
Planning Time: 0.4 ms
Execution Time: 12.9 ms

Как читать

ПолеЗначение
cost=a..bОценка: стоимость до первой строки .. до последней (условные единицы)
rowsОценка числа строк
actual time=a..bФактическое время до первой и последней строки (мс, на один loop)
actual rowsФактическое число строк (на один loop)
loopsСколько раз выполнялся узел (умножайте время и строки)
Buffers: shared hit / readСтраницы из кеша / с диска
Rows Removed by FilterСколько строк прочитано и отброшено

Основные узлы

УзелСмысл
Seq ScanЧтение всей таблицы
Index Scan / Index Only ScanПоиск по индексу (без обращения к таблице во втором случае)
Bitmap Heap ScanМного строк через индекс, чтение страниц пачками
Nested LoopДля каждой строки снаружи — поиск внутри (хорошо с индексом и малым числом строк)
Hash JoinХеш-таблица по одной стороне
Merge JoinСлияние двух отсортированных потоков
SortСортировка (Sort Method: external merge Disk — не хватило work_mem)
HashAggregate / GroupAggregateГруппировка
LimitОстановка после n строк

Сигналы проблем

  • rows=10 в оценке и actual rows=500000 — устаревшая статистика (ANALYZE), коррелированные столбцы (расширенная статистика), функция в условии.
  • Seq Scan + Rows Removed by Filter: 999990 — не хватает индекса или он не применим.
  • Nested Loop с loops=100000 и Seq Scan внутри — нет индекса на столбце соединения.
  • Sort ... Disk — увеличить work_mem для запроса или убрать сортировку индексом.
  • Высокий Buffers: read — данные не в кеше, возможно, читается лишнее.

Инструменты

  • explain.dalibo.com и explain.depesz.com — визуализация.
  • auto_explain — логировать планы медленных запросов автоматически.
  • pg_stat_statements — найти, какие запросы суммарно дороже всего.

Частые ошибки

  • Анализировать только EXPLAIN без ANALYZE — оценки могут быть далеки от реальности.
  • EXPLAIN ANALYZE для DELETE в проде без ROLLBACK.
  • Тестировать на пустой локальной базе — планы на маленьких таблицах другие.
  • Забыть умножить actual time на loops.

Что важно помнить

  • EXPLAIN — план и оценки, ANALYZE — факт.
  • Сравнивайте оценку и факт по числу строк.
  • Ищите самые дорогие узлы, а не любой Seq Scan.

Мини-шпаргалка

  • EXPLAIN (ANALYZE, BUFFERS) query.
  • BEGIN; EXPLAIN ANALYZE <DML>; ROLLBACK;.
  • rows vs actual rows.
  • pg_stat_statements, auto_explain.

Документация

PostgreSQL: Using EXPLAIN, PostgreSQL: pg_stat_statements.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам