Как разобрать план запроса EXPLAIN ANALYZE - руководство разработчика

Запрос работал секунду, а сегодня тормозит десять. Сервер тот же, таблицы те же. В чём дело? Ответ внутри одной команды - EXPLAIN ANALYZE. Это руководство поможет читать её вывод без боли, даже если вы видите его впервые.

Что такое EXPLAIN ANALYZE и зачем он нужен

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

Главное отличие от простого EXPLAIN: последний только строит план без запуска. EXPLAIN ANALYZE реально выполняет запрос - и сравнивает ожидания планировщика с фактом.

⚠️ Важно: EXPLAIN ANALYZE UPDATE/DELETE изменит данные. Оборачивайте в транзакцию и делайте ROLLBACK.

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

Команда	Выполняет запрос	Показывает actual time	Показывает actual rows
EXPLAIN	Нет	Нет	Нет
EXPLAIN ANALYZE	Да	Да	Да

Используйте EXPLAIN для быстрой проверки плана без побочных эффектов. EXPLAIN ANALYZE - когда нужна точная диагностика.

Три стадии обработки запроса PostgreSQL

Прежде чем читать вывод, понимите, что происходит внутри. PostgreSQL обрабатывает каждый запрос в три этапа:

1. Парсинг - проверка синтаксиса (~1 мс). При ошибке здесь сразу получите syntax error.

2. Планирование - оптимизатор генерирует несколько планов и выбирает самый дешёвый по стоимости (~10–100 мс для сложных запросов). Большинство проблем производительности - именно здесь.

3. Выполнение - реальная работа с данными (~10 мс – несколько секунд).

Планировщик принимает решения на основе статистики. Нет свежей статистики - нет правильного плана.

Что означает cost: единицы и формула

cost в выводе EXPLAIN - не миллисекунды. Это условные единицы, которые планировщик использует для сравнения вариантов. Базовая единица - обход одной страницы диска.

По умолчанию:

• seq_page_cost = 1.0 - стоимость последовательного чтения одной страницы
• cpu_tuple_cost = 0.01 - стоимость обработки одной строки CPU

Пример формулы для простого Seq Scan на таблице с 345 страницами и 10 000 строк:

Index Scan using orders_pkey on orders
(cost=0.43..8.45 rows=1 width=68)
(actual time=0.017..0.018 rows=1 loops=1)
Planning Time: 0.120 ms
Execution Time: 0.045 ms

Читается просто, как только знаешь ключ.

Четыре ключевых числа в каждом узле

В строке (cost=X..Y rows=Z width=W) - оценка планировщика. В строке (actual time=A..B rows=C loops=D) - факт.

Поле	Оценка	Факт
Стоимость	cost=0.43..8.45	-
Строки	rows=1	rows=1
Время	-	actual time=0.017..0.018 мс
Проходы	-	loops=1

Главный сигнал тревоги: rows=10 (оценка) vs actual rows=1 000 000 (факт). Расхождение в тысячи раз - значит статистика устарела.

Дерево узлов: читать снизу вверх

Вывод EXPLAIN ANALYZE - это дерево операций. Нижние узлы выполняются первыми и передают данные вверх.

Limit
  ↑
Sort
  ↑
Hash Join
  ↑
  ├── Seq Scan on orders   (выполняется первым)
  └── Hash
      ↑
      └─ Seq Scan on customers

Правило: начинайте анализ с самого вложенного (нижнего) узла с высоким cost. Там обычно и проблема.

Что такое loops и как считать реальное время

loops=10 означает, что узел выполнялся 10 раз (например, при Nested Loop для каждой строки внешней таблицы).

actual time в выводе - среднее за один проход. Чтобы получить суммарное время узла:

Реальное время = actual time × loops

Если actual time=3.000..3.003 и loops=10 - узел занял ~30 мс суммарно.

BUFFERS: shared hit vs read

Параметр BUFFERS (включается автоматически с ANALYZE) показывает нагрузку на I/O:

• shared hit=36 - 36 блоков взяты из кэша (быстро)
• read=6 - 6 блоков читались с диска (медленно)

Большое значение read при малом shared hit - сигнал, что shared_buffers слишком мал. Рекомендуется 25% ОЗУ сервера.

Три типа сканирования таблицы

Планировщик выбирает метод сканирования исходя из размера таблицы, наличия индекса и процента выбираемых данных.

Seq Scan: когда норма, когда проблема

Seq Scan - последовательное чтение всей таблицы от первой строки до последней. PostgreSQL использует его, когда:

• Нет подходящего индекса

• Выбирается большой процент таблицы (>5–10% строк)

• Таблица маленькая

Seq Scan становится проблемой, когда таблица содержит сотни тысяч строк, а запрос возвращает 1–2% данных. На таблице в 5 млн строк это означает чтение миллионов блоков ради сотни результатов.

-- Seq Scan: плохой знак на большой таблице
EXPLAIN SELECT * FROM customers WHERE email = 'user@example.com';

-- Seq Scan on customers (cost=0.00..50000.00 rows=1 width=100)

-- Filter: (email = 'user@example.com')

Index Scan и Bitmap Scan: разница и сценарии

Index Scan использует B-tree индекс для прямого поиска строк. Сложность: O(log N). Добавление индекса на поле email превращает полное сканирование миллиона строк в несколько операций - ускорение до 10 000×.

Bitmap Index Scan появляется при сложных фильтрах с несколькими индексами. Он строит битовую карту совпадений из каждого индекса, объединяет через BitmapAnd/BitmapOr, затем извлекает строки. Это золотая середина между Seq Scan и прямым Index Scan.

-- После создания индекса
CREATE INDEX CONCURRENTLY idx_customers_email ON customers (email);

ANALYZE customers;

EXPLAIN
SELECT
    *
FROM
    customers
WHERE
    email = 'user@example.com';

-- Index Scan using idx_customers_email on customers
-- (cost=0.29..1.00 rows=1 width=100)

Index-Only Scan: самый быстрый вариант

Index Only Scan означает, что PostgreSQL нашёл все нужные данные прямо в индексе и не обращался к таблице. Это возможно, когда все запрашиваемые колонки входят в индекс.

CREATE INDEX ON orders(status, created_at DESC);
-- Запрос только по этим полям → Index Only Scan

Три стратегии JOIN в планах запросов

JOIN - самое частое место для проблем в сложных запросах. Понимание трёх стратегий помогает быстро диагностировать тормоза.

Hash Join: когда и почему самый быстрый

Hash Join - стандартный метод для соединения больших таблиц. Алгоритм:

1. Читает меньшую таблицу в хеш-таблицу в RAM
2. Сканирует большую таблицу и ищет совпадения в хеше

Сложность O(N+M) - линейная. Обычно самый быстрый JOIN. Работает хорошо, пока данные влезают в work_mem. Если таблица не помещается - данные выгружаются на диск (spill), и время резко растёт.

-- Если Hash Join тормозит - увеличьте work_mem на сессию
SET work_mem = '256MB';

Nested Loop: плюсы и подводные камни

Nested Loop работает через вложенный цикл: для каждой строки внешней таблицы сканирует внутреннюю.

Когда хорош:

• Внешняя таблица маленькая (≤ нескольких тысяч строк)
• Внутренняя таблица хорошо индексирована по полю JOIN

Когда плох: внешняя таблица большая, а поиск во внутренней дорогой. При 100к внешних строк и 10 мс поиска каждой - суммарно 1000 секунд.

Если видите Nested Loop на больших таблицах - подозревайте устаревшую статистику или маленький work_mem.

Merge Join: редкий гость в планах

Merge Join требует обе таблицы отсортированными по ключу JOIN. PostgreSQL выбирает его, когда индекс уже обеспечивает нужный порядок.

Сложность O((N log N) + (M log M) + (N+M)). Обычно проигрывает Hash Join, кроме случаев, когда данные уже отсортированы.

-- Принудительно посмотреть конкретный тип JOIN (только для тестов!)
SET enable_hashjoin = off;
SET enable_nestloop = off;
EXPLAIN ANALYZE SELECT ...;
-- Потом обязательно: RESET enable_hashjoin; RESET enable_nestloop;

Четыре типичные проблемы и как их исправить

Практика показывает: 90% медленных запросов в PostgreSQL - это четыре повторяющиеся ситуации.

Проблема 1: rows ≠ actual rows → VACUUM ANALYZE

Признак: в выводе EXPLAIN ANALYZE планировщик оценил rows=100, а факт actual rows=900 000. Расхождение в тысячи раз.

Причина: статистика устарела. Последний ANALYZE был давно или autovacuum отключён.

Решение:

VACUUM ANALYZE table_name;

-- Проверьте autovacuum:
SHOW autovacuum; -- должно быть 'on'

После этого повторите EXPLAIN ANALYZE. Если rows ≈ actual rows - проблема устранена.

Проблема 2: Seq Scan на большой таблице → CREATE INDEX

Признак: Seq Scan на таблице в сотни тысяч строк с фильтром по конкретному значению.

Решение:

-- CONCURRENTLY - создаёт индекс без блокировки таблицы
CREATE INDEX CONCURRENTLY idx_orders_email ON customers(email);
ANALYZE customers;

На таблице в 10 млн строк с нужным индексом запрос ускоряется с 700 мс до ~1 мс - результат из реального кейса с составным индексом (status, created_at DESC).

Составной индекс помогает, когда фильтр и сортировка по разным полям:

-- Для запроса: WHERE status='completed' ORDER BY created_at DESC
CREATE INDEX CONCURRENTLY idx_orders_status_date
  ON orders(status, created_at DESC);

Проблема 3: Nested Loop вместо Hash Join → work_mem

Признак: медленный Nested Loop там, где ожидается Hash Join. Execution Time в секундах вместо миллисекунд.

Причина: work_mem слишком мал, чтобы поместить хеш-таблицу в память.

Решение:

-- На сессию - без перезапуска сервера
SET
    work_mem = '256MB';

-- Для production в postgresql.conf:
-- work_mem = 256MB
-- Применить без рестарта:
SELECT
    pg_reload_conf ();

Проблема 4: параллелизм не работает → max_parallel_workers

Признак: в плане Workers Planned: 4, но Workers Launched: 0.

SET
    max_parallel_workers_per_gather = 4;

SET
    work_mem = '512MB';

-- В postgresql.conf для production:
-- max_worker_processes = 8
-- max_parallel_workers = 8
-- max_parallel_workers_per_gather = 4

Типичный выигрыш: запрос COUNT на большой таблице с 10 сек (1 ядро) → 3 сек (4 ядра).

Инструменты для анализа планов

Текстовый вывод EXPLAIN ANALYZE при большом запросе выглядит устрашающе. Визуализаторы упрощают работу.

Онлайн-визуализаторы планов

Скопируйте вывод EXPLAIN ANALYZE и вставьте в один из сервисов:

• explain.depesz.com - акцентирует самые дорогие узлы цветом, удобно для быстрого поиска узкого места

• explain.dalibo.com - строит интерактивный граф дерева выполнения

• pgmustard.com - даёт автоматические рекомендации по оптимизации

Для передачи плана программно используйте FORMAT JSON:

EXPLAIN (ANALYZE, FORMAT JSON) SELECT ...;

pg_stat_statements: мониторинг медленных запросов

Чтобы не ловить медленные запросы руками, подключите расширение:

-- В postgresql.conf добавьте строку и перезапустите:
-- shared_preload_libraries = 'pg_stat_statements'
-- После перезапуска:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Найти 10 самых медленных запросов (PostgreSQL 16+):
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time
FROM
    pg_stat_statements
WHERE
    mean_exec_time > 100
ORDER BY
    mean_exec_time DESC
LIMIT
    10;

В PostgreSQL 15 и старше используйте total_time и mean_time вместо total_exec_time/mean_exec_time.

Проверяйте ТОП медленных запросов раз в неделю и разбирайте их с EXPLAIN ANALYZE.

Чек-лист оптимизации: 5 шагов

Когда запрос тормозит, идите по этой цепочке:

1. Запустите EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <запрос>
2. rows ≈ actual rows? Нет → VACUUM ANALYZE и повтор
3. Есть Seq Scan на большой таблице по полю без индекса? → CREATE INDEX CONCURRENTLY
4. Nested Loop вместо Hash Join на большом JOIN? → SET work_mem = '256MB'
5. Много read в Buffers, мало shared hit? → увеличьте shared_buffers (25% RAM)

Авторская ремарка: Самая частая ошибка - создавать индексы до запуска VACUUM ANALYZE. Планировщик видит новый индекс, но со старой статистикой не знает, как им правильно воспользоваться. Сначала статистика - потом индекс.

Альтернативное мнение

Некоторые разработчики полагают, что добавление индексов - всегда улучшение. Это не так. Каждый индекс замедляет INSERT, UPDATE, DELETE и занимает место на диске. Планировщик иногда намеренно выбирает Seq Scan для небольших таблиц (< 5–10 тыс. строк) или при выборке большого процента данных - это правильное решение, не баг.

Не создавайте индексы по интуиции. Создавайте их после того, как EXPLAIN ANALYZE показал конкретную проблему.

Интересный факт

cost в PostgreSQL - это не абсолютное время и не байты. Это безразмерная величина, которую планировщик придумал для сравнения планов между собой. Параметры seq_page_cost, cpu_tuple_cost и другие - просто коэффициенты. Поэтому cost=445.00 на одном сервере может выполняться за 0.1 мс, а на другом - за 2 мс.

FAQ

Что такое EXPLAIN ANALYZE в PostgreSQL?

EXPLAIN ANALYZE - команда, которая реально выполняет запрос и показывает план выполнения с фактической статистикой: время работы каждого шага, реальное и ожидаемое число строк. Главный инструмент диагностики медленных запросов.

Чем EXPLAIN отличается от EXPLAIN ANALYZE?

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

Почему PostgreSQL выбирает Seq Scan вместо Index Scan?

Планировщик выбирает Seq Scan, если нет подходящего индекса, выбирается >5–10% строк таблицы, или таблица маленькая. На большой таблице с маленькой выборкой - создайте индекс через CREATE INDEX CONCURRENTLY и запустите ANALYZE.

Что означает расхождение rows и actual rows?

Расхождение в тысячи раз - признак устаревшей статистики. Запустите VACUUM ANALYZE table_name. Проверьте SHOW autovacuum; - должно быть on.

Что такое cost и почему он не в миллисекундах?

cost - условные единицы для сравнения планов между собой. Базовая единица - чтение одной страницы диска (seq_page_cost=1.0). cost=0.43..8.45 означает старт 0.43, итог 8.45 единиц. В разных конфигурациях серверов одинаковый cost даёт разное реальное время.

Как безопасно использовать EXPLAIN ANALYZE с UPDATE?

Оборачивайте в транзакцию: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK; - получите реальную статистику без изменения данных.

Какие инструменты помогают читать вывод EXPLAIN ANALYZE?

explain.depesz.com подсвечивает дорогие узлы, explain.dalibo.com строит граф, pgmustard.com даёт автоматические советы. Для парсинга программно: EXPLAIN (ANALYZE, FORMAT JSON).

Источники

• Postgres Professional - «14.1. Использование EXPLAIN» (официальная документация PostgreSQL на русском) - https://postgrespro.ru/docs/postgresql/current/using-explain

• Habr, «Программный Продукт» - «PostgreSQL Query Planner: Практический гид по EXPLAIN ANALYZE» - https://habr.com/ru/companies/ppr/articles/978100/

• sqlcode.ru - «Seq Scan в PostgreSQL» - https://www.sqlcode.ru/index.php/postgresqlopti/explain-analyze/seq-scan

• wiki.postgresql.org - «Using EXPLAIN/ru» - https://wiki.postgresql.org/wiki/Using_EXPLAIN/ru

• Postgres Professional - «EXPLAIN - показать план выполнения оператора» (справка по команде) -  https://postgrespro.ru/docs/postgresql/current/sql-explain