Queries & Walks

EXPLAIN ANALYZE без страха

· 9 мин чтения
PostgreSQL Performance

Когда я показываю новым коллегам план выполнения запроса впервые, реакция обычно одна и та же: стена текста, вложенные скобки, странные слова вроде «Hash Semi Join», и общее ощущение, что это писали не для людей. Хорошая новость: вам не нужно понимать план целиком с первого раза. Нужно уметь найти в нём три-четыре вещи — и уже это даёт девяносто процентов практической пользы.

Сначала — разница между EXPLAIN и EXPLAIN ANALYZE

EXPLAIN без ANALYZE показывает только то, что планировщик предполагает: оценочную стоимость, оценочное число строк. Он не выполняет запрос. EXPLAIN ANALYZE — выполняет запрос по-настоящему и показывает, что произошло на самом деле, включая реальное время и реальное число строк. Разница между этими двумя режимами — как между прогнозом погоды и показаниями термометра за окном.

Осторожно

EXPLAIN ANALYZE на INSERT, UPDATE или DELETE реально изменит данные — план строится на выполненном запросе, а не на симуляции. Для таких запросов оборачивайте в транзакцию с ROLLBACK, если хотите просто посмотреть план.

BEGIN; EXPLAIN ANALYZE DELETE FROM ...; ROLLBACK;

Пример запроса

sql 
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42;

Возможный результат:

plan 
Index Scan using idx_orders_customer_id on orders
  (cost=0.43..8.56 rows=12 width=94)
  (actual time=0.031..0.048 rows=11 loops=1)
  Index Cond: (customer_id = 42)
  Buffers: shared hit=4
Planning Time: 0.089 ms
Execution Time: 0.071 ms

Четыре вещи, на которые смотреть в первую очередь

1. Тип узла (node type)

Seq Scan, Index Scan, Index Only Scan, Bitmap Heap Scan, Nested Loop, Hash Join — это способ, которым PostgreSQL достаёт или соединяет данные. Не каждый Seq Scan — плохо, не каждый Index Scan — хорошо; это зависит от объёма данных, о котором мы говорили в статье про индексы.

2. rows: оценка против реальности

Сравните rows=12 в оценке (первая часть строки, до actual) с rows=11 в реальности (внутри actual time=...). Если эти числа отличаются в разы — например, планировщик ожидал 50 строк, а получил 50000 — это почти всегда признак устаревшей статистики. Решение обычно простое:

sql 
ANALYZE orders;

3. Execution Time против Planning Time

Если Planning Time сопоставим с Execution Time или даже больше него — это отдельная история, часто связанная с очень большим числом партиций или сложными представлениями (views), а не с самим запросом.

4. Buffers: откуда читались данные

shared hit — страницы, найденные в buffer cache PostgreSQL (быстро). shared read — страницы, прочитанные с диска (медленнее, особенно на холодном кэше). Флаг BUFFERS в EXPLAIN стоит включать почти всегда — без него вы не увидите, действительно ли запрос упирается в диск.

Из практики

Как-то раз запрос «внезапно» стал медленным ровно после деплоя, который вообще не трогал эту часть кода. EXPLAIN ANALYZE показал огромный shared read вместо привычного shared hit. Оказалось, деплой перезапустил инстанс, buffer cache очистился, а «медленный запрос» на самом деле был первым холодным чтением после рестарта. Через десять минут всё вернулось к норме само.

Более сложный план: JOIN и агрегация

Одиночный Index Scan читается легко. Сложнее становится, когда в плане несколько уровней вложенности. Возьмём запрос с соединением и группировкой:

sql 
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, count(*) AS orders_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.name;
plan 
HashAggregate (actual time=42.100..42.980 rows=310 loops=1)
  Group Key: c.name
  ->  Hash Join (actual time=1.220..38.410 rows=9820 loops=1)
        Hash Cond: (o.customer_id = c.id)
        ->  Index Scan using idx_orders_date on orders o
              (actual time=0.040..12.330 rows=9820 loops=1)
              Index Cond: (order_date >= '2026-01-01')
        ->  Hash (actual time=1.050..1.050 rows=1500 loops=1)
              ->  Seq Scan on customers c
                    (actual time=0.008..0.410 rows=1500 loops=1)
Execution Time: 43.210 ms

Читаем снизу вверх: сначала таблица customers целиком читается через Seq Scan (она маленькая — 1500 строк, поэтому это нормально) и превращается в хэш-таблицу в памяти. Затем PostgreSQL проходит по отфильтрованным заказам через Index Scan и соединяет каждую строку с хэш-таблицей клиентов — это Hash Join. Наконец HashAggregate группирует результат по имени клиента. Самый дорогой узел здесь — Hash Join с 38 миллисекундами из 43 общих: если бы нужно было ускорять этот запрос, начинать стоило бы именно с него.

Когда сортировка уходит на диск

Отдельный узел, который стоит уметь замечать — Sort с деталью Sort Method: external merge Disk вместо quicksort Memory. Это значит, что сортировке не хватило work_mem, и PostgreSQL пришлось использовать временные файлы на диске, что заметно медленнее:

plan 
Sort (actual time=210.442..340.117 rows=500000 loops=1)
  Sort Key: amount DESC
  Sort Method: external merge  Disk: 48120kB

Увеличение work_mem для конкретной сессии или запроса часто решает проблему полностью, превращая внешнюю сортировку на диске в быструю сортировку в памяти.

Таблица-шпаргалка

Что видитеО чём это говорит
Большая разница estimated / actual rowsУстаревшая статистика — нужен ANALYZE
Много shared read, мало shared hitДанные не помещаются в кэш или холодный старт
Nested Loop с большим числом итераций (loops)Возможно, стоит помочь планировщику индексом или JOIN-порядком
Sort с external merge в деталяхНе хватает work_mem, сортировка ушла на диск
Узел с явно наибольшим actual time относительно родителяЭто и есть узкое место запроса

Инструменты, которые помогают

Текстовый план можно читать глазами, но для больших планов удобнее визуализаторы вроде explain.dalibo.com или depesz.com — они подсвечивают самые дорогие узлы автоматически и раскрашивают дерево плана. Формат EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) отлично подходит для таких инструментов, а не только для человеческого чтения.

Коротко

  • Начинайте с четырёх вещей: тип узла, rows (оценка vs реальность), время, buffers.
  • Расхождение оценки и реальности — сигнал делать ANALYZE, а не сразу переписывать запрос.
  • Читайте план снизу вверх, ищите узел с наибольшим вкладом во время выполнения.
  • external merge Disk в Sort почти всегда лечится увеличением work_mem.
  • Не обязательно понимать план целиком — обычно достаточно найти самое дорогое место.
← Back to articles