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

Зачем читать EXPLAIN

Когда SQL-запрос работает медленно, два вопроса: «что происходит внутри?» и «как ускорить?». EXPLAIN ANALYZE отвечает на первый. Без него любая оптимизация — гадание.

Команда показывает план выполнения запроса: какие таблицы читаются, какими методами, как соединяются, сколько времени тратится на каждую операцию. Для middle+ аналитика умение читать план — базовый навык.

Синтаксис простой:

EXPLAIN ANALYZE SELECT ...;

Важное предупреждение: EXPLAIN ANALYZE реально выполняет запрос. Для SELECT это безопасно. Для UPDATE/DELETE — нет, изменит данные. Для таких случаев используйте обычный EXPLAIN (только план без выполнения) или оборачивайте в транзакцию с ROLLBACK.

Структура вывода

Базовый пример:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

Ответ:

Index Scan using idx_orders_user on orders
    (cost=0.43..8.45 rows=1 width=64)
    (actual time=0.023..0.025 rows=1 loops=1)
    Index Cond: (user_id = 42)
Planning Time: 0.102 ms
Execution Time: 0.054 ms

Разберём блоки:

Тип операции: «Index Scan using idx_orders_user» — используется индекс на колонке user_id. Быстро.

cost=0.43..8.45: оценка стоимости запроса. Первое число — стартовая (время до первой строки), второе — общая. Единицы условные, сравниваются между планами, абсолютные значения бессмысленны.

rows=1: ожидаемое число строк. Если сильно расходится с actual rows — статистика устарела.

actual time=0.023..0.025: реальное время в миллисекундах.

rows=1 loops=1: сколько раз операция выполнялась (loops) и сколько строк вернула.

Planning Time / Execution Time: время на построение плана и выполнение. Обычно execution доминирует.

Виды сканов

Таблица может читаться разными способами. Каждый имеет свои характеристики.

Seq Scan — последовательный скан всей таблицы. Читает каждую строку. Для маленьких таблиц — ок. Для больших без фильтра — тормоза.

Index Scan — скан через индекс. Быстро находит нужные строки, но делает random reads из таблицы за каждой.

Index Only Scan — самый быстрый вариант. Данные целиком берутся из индекса, в таблицу не заходит. Работает, когда все нужные колонки есть в индексе (covering index).

Bitmap Heap Scan + Bitmap Index Scan — гибрид. Через индекс собирается bitmap нужных строк, потом они читаются из таблицы sequentially. Хорош, когда фильтр выбирает много строк — избегает медленных random reads.

Что считать хорошим/плохим — зависит от ситуации. Seq Scan на 10-миллионной таблице — плохо. Index Scan с миллионом loops — тоже плохо. Index Only Scan на 100 строк — отлично.

JOIN-ы

Три основных алгоритма соединения таблиц.

Nested Loop — для каждой строки внешней таблицы сканируется внутренняя. Сложность O(N × M). Эффективен, когда внешняя таблица маленькая, а внутренняя имеет хороший индекс по join-ключу.

Hash Join — строит хэш-таблицу по одной стороне, потом сканирует другую и ищет совпадения. Сложность O(N + M). Хорош для больших таблиц без индексов.

Merge Join — обе таблицы сортируются по join-ключу, потом идёт merge. Эффективен, когда данные уже отсортированы (через индексы).

Частая проблема: оптимизатор выбирает Nested Loop, когда стоило Hash Join. Это случается при плохой статистике. EXPLAIN покажет:

Nested Loop (actual time=0.1..5432 rows=1000000 loops=1)
    -> Seq Scan on users (rows=1000)
    -> Index Scan on orders (loops=1000)

Миллион строк из Nested Loop с 1000 loops — признак проблемы. Hash Join бы справился за секунды.

Как чинить: убедиться, что статистика актуальная (ANALYZE orders), упростить условия, добавить нужные индексы.

Сортировки

Когда в запросе есть ORDER BY, GROUP BY, DISTINCT, может появиться Sort-нода:

Sort Method: quicksort Memory: 125kB
    -> Seq Scan on orders

quicksort Memory — сортировка в памяти, быстро. Плохой сценарий:

Sort Method: external merge Disk: 245MB

Сортировка пошла на диск, потому что данные не влезли в память (work_mem). Это сильно медленно. Решения: добавить индекс, соответствующий ORDER BY (тогда сортировка не нужна), увеличить work_mem, упростить запрос.

Оптимально — чтобы план использовал индекс для сортировки, без явной Sort-ноды:

Index Scan using idx_orders_created_at on orders

Индекс уже хранит данные в отсортированном виде.

Agregates

Для GROUP BY Postgres использует HashAggregate или GroupAggregate.

HashAggregate — строит хэш-таблицу в памяти, группирует. Быстро для небольшого количества групп.

GroupAggregate — сортирует данные, потом группирует. Медленнее, но требует меньше памяти.

Если план показывает HashAggregate с огромным потреблением памяти или переходит на диск — возможно, слишком много групп. Для агрегации по user_id на миллиард пользователей это норма, но можно попробовать pre-aggregation в материализованной таблице.

Разобраться в плане запроса на уровне собеседования — важный скилл для middle+. В тренажёре Карьерник есть задачи на оптимизацию запросов с детальными разборами планов выполнения.

Rows estimates

В плане рядом идут estimated и actual rows. Большое расхождение — признак проблем со статистикой.

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

Оптимизатор думал, что будет 100 строк, а получил миллион. Следующие операции (JOIN, Sort) были спланированы под 100, но реально обрабатывают миллион. Результат — медленный запрос.

Лечится вызовом ANALYZE table_name; — обновление статистики. Postgres автоматически запускает ANALYZE для таблиц, которые сильно изменились, но иногда отстаёт.

Для часто меняющихся таблиц можно увеличить частоту auto-analyze через настройки autovacuum.

BUFFERS и реальная стоимость

По умолчанию EXPLAIN не показывает, сколько данных прочитано с диска. Включите это явно:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Получите дополнительные строки:

Buffers: shared hit=125 read=50

• shared hit — блоки, которые были в memory cache (быстро).
• shared read — блоки, прочитанные с диска (медленно).

Если большая часть — read, запрос будет медленным на «холодной» БД. После нескольких выполнений Postgres закэширует и всё станет read=0, hit=200.

Это важно для бенчмаркинга: первый запуск показывает «холодную» производительность, последующие — «горячую».

Типичные паттерны проблем

Seq Scan на большой таблице с узким фильтром. Индекс бы помог: CREATE INDEX ON orders (user_id);.

Nested Loop с millions rows. Hash Join работал бы лучше. Обычно чинится через ANALYZE или переписывание запроса.

Sort на диск. Добавить индекс для ORDER BY или упростить запрос.

Множество Filter после Index Scan. Индекс частично помогает, но остаются фильтры. Подумайте о composite index с дополнительными колонками.

HashAggregate на большом объёме. Pre-aggregate в материализованную таблицу или увеличить work_mem.

Отладка: пошаговый алгоритм

Когда запрос медленный:

Шаг первый: запустите EXPLAIN ANALYZE. Смотрите execution time.

Шаг второй: найдите самую тяжёлую операцию — обычно у неё самое большое actual time.

Шаг третий: посмотрите тип этой операции. Seq Scan? Индекс. Nested Loop? Проверить статистику. Sort external merge? Добавить индекс или увеличить work_mem.

Шаг четвёртый: исправьте, перезапустите EXPLAIN ANALYZE. Сравните время.

Это итеративный процесс. Редко бывает одно узкое место — обычно несколько, исправляются по очереди.

Ошибки в интерпретации

Первая — смотреть только на cost. Cost — это оценка, actual time — реальность. Если cost маленький, а actual большой — оптимизатор ошибся в оценке.

Вторая — полагаться на одно выполнение. Первый запуск медленный из-за cold cache. Запустите 2-3 раза, смотрите стабильные цифры.

Третья — сравнивать абсолютные cost между разными запросами. Cost условный, сравнивается только внутри одного запроса между планами.

Четвёртая — игнорировать loops в Nested Loop. Если внутренняя операция имеет loops=100000, её actual time умножается на 100000.

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

• Оптимизация SQL-запросов
• Индексы SQL
• Как ускорить SQL-запрос
• EXPLAIN SQL: шпаргалка

FAQ

EXPLAIN или EXPLAIN ANALYZE?

EXPLAIN — только план, быстро, без выполнения. EXPLAIN ANALYZE — с реальными timings, но запрос действительно выполняется. Для SELECT используйте ANALYZE. Для UPDATE/DELETE — EXPLAIN (без ANALYZE).

Как ускорить запрос, если план выглядит нормально?

Проверьте BUFFERS — много read означает cold cache. После 2-3 прогонов должно быть hit. Если всё равно медленно — проверьте конкурентные запросы, locks, общую загрузку сервера.

Postgres игнорирует мой индекс?

Возможно, статистика устарела (запустите ANALYZE), или условие не подходит под индекс (функция над колонкой, cast типов), или таблица маленькая (seq scan быстрее).

Как сделать план красивым для анализа?

Используйте инструменты вроде explain.depesz.com или pgMustard. Они визуализируют план, подсвечивают проблемные места. Очень помогают для больших запросов.