Анализ запросов (EXPLAIN)

Вы написали SQL-запрос. Он возвращает правильные данные. Но работает он секунду, хотя данных всего несколько тысяч строк. Почему? Как понять, где запрос тормозит, и что нужно исправить — добавить индекс, переписать JOIN, изменить порядок условий?

EXPLAIN — это команда в SQL (поддерживается PostgreSQL, MySQL, SQLite, Oracle, и другими СУБД), которая показывает план выполнения запроса. Вместо того чтобы гадать, как база данных будет искать данные, вы можете заглянуть “под капот” и увидеть точный план: какие индексы используются, в каком порядке объединяются таблицы, сколько строк планируется прочитать.

Что такое план выполнения запроса

Когда вы отправляете SQL-запрос в базу данных, она не выполняет его “в лоб” в том порядке, как вы написали.

Процесс выполнения запроса:

Парсинг — проверка синтаксиса.
Разбор (bind). Преобразование в промежуточное представление.
Оптимизация. Планировщик (query planner / optimizer) строит несколько возможных планов выполнения и выбирает тот, который, по его оценке, будет самым дешёвым.
Выполнение. Исполнение выбранного плана.

EXPLAIN показывает именно план, который получился на этапе оптимизации, без реального выполнения запроса. Он помогает понять, что будет делать база данных.

Базовый синтаксис:

EXPLAIN SELECT * FROM users WHERE last_name = 'Иванов';

Вы получите текстовое описание плана (например, “Seq Scan” — последовательное сканирование всей таблицы, или “Index Scan” — поиск по индексу).

EXPLAIN ANALYZE

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

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE last_name = 'Иванов';

EXPLAIN без ANALYZE — безопасен (не выполняет запрос), но даёт только оценку (часто неточную). EXPLAIN ANALYZE показывает истинное время и количество строк, но он:

Выполняет запрос (если это UPDATE/INSERT/DELETE, то данные будут изменены — оберните в транзакцию с ROLLBACK).
Требует осторожности на production (может нагрузить систему или затронуть бизнес-данные).

Типовые узлы плана (на примере PostgreSQL)

Seq Scan (полное сканирование таблицы)

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

Seq Scan on users  (cost=0.00..203.50 rows=50 width=100)
  Filter: (last_name = 'Иванов')

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

Когда хорошо: таблица очень маленькая (до 100-200 строк). Когда плохо: таблица большая (миллионы строк) — каждый запрос будет сканировать всё, что крайне медленно.

Index Scan (выборка по индексу)

База данных использует индекс, чтобы быстро найти строки, соответствующие условию.

Index Scan using idx_users_last_name on users  (cost=0.29..8.32 rows=1 width=100)
  Index Cond: (last_name = 'Иванов')

Возникает, когда в условии WHERE есть поле, по которому построен индекс (или композитный индекс, включающий это поле). Хорошо для точечных запросов, возвращающих небольшой процент строк.

Осторожно: если условие last_name LIKE 'Ива%', индекс может быть использован (поиск по префиксу). Если LIKE '%ванов' — скорее всего, не будет.

Index Only Scan

Индекс содержит все необходимые поля запроса (покрывающий индекс). База данных читает только индекс, не обращаясь к самой таблице. Это самый быстрый вариант.

Index Only Scan using idx_users_last_name on users  (cost=0.29..4.30 rows=1 width=100)
  Index Cond: (last_name = 'Иванов')

Bitmap Scan

Комбинация последовательного сканирования и использования индекса. Сначала по индексу находится набор страниц (блоков) таблицы, которые нужно прочитать, затем эти страницы читаются в отсортированном порядке (что эффективнее, чем читать их вразнобой).

Bitmap Heap Scan on users  (cost=4.30..12.50 rows=10 width=100)
  Recheck Cond: (last_name = 'Иванов')
  -> Bitmap Index Scan on idx_users_last_name

Возникает, когда индекс возвращает много строк (условие неселективно) и чтение каждой строки по отдельности через Index Scan было бы слишком дорого.

Nested Loop Join (вложенные циклы)

Для каждой строки из внешней таблицы база данных ищет совпадения во внутренней таблице (часто с помощью индекса). Эффективен, когда одна таблица маленькая, а во второй есть индекс по ключу соединения.

Hash Join

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

Merge Join

Обе таблицы сортируются по ключу соединения, затем происходит “слияние” двух отсортированных списков. Требует предварительной сортировки. Полезен, когда данные уже отсортированы (есть индекс) или когда соединение не по равенству (например, >=).

Sort

База данных принудительно сортирует строки (например, для ORDER BY без индекса). Для больших наборов данных сортировка может быть дорогой (использовать work_mem).

Как читать вывод EXPLAIN

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

Каждая строка плана — это один “узел” операции (scan, join, sort). Узлы вложены: внутренние узлы выполняются до внешних. Обычно план читается от самого вложенного узла наружу.

Важные колонки:

cost — оценка стоимости (первое число = стоимость получения первой строки, второе = стоимость получения всех строк). Единицы условные (обычно “стоимость чтения страницы с диска”).
rows — предполагаемое количество строк, которое вернёт данный узел (по статистике таблицы).
width — средняя ширина строки в байтах.
actual time (только в EXPLAIN ANALYZE) — реальное время выполнения узла.
actual rows — реальное количество строк.

Как понять, что запрос “плохой”

Признак	Возможная причина	Что делать
Seq Scan на большой таблице в условиях `WHERE`	Нет индекса по полю фильтрации	Создать индекс
Nested Loop Join с большой внешней таблицей	Нет индекса на внутренней таблице	Добавить индекс на поле соединения
Sort без необходимости	Нет индекса, поддерживающего `ORDER BY` или `GROUP BY`	Создать индекс по полям сортировки или увеличить `work_mem`
Огромное расхождение между rows (оценка) и actual rows	Устаревшая статистика (аналитик)	Выполнить `ANALYZE` (сбор статистики)
Bitmap Heap Scan с Recheck Cond	Низкая селективность индекса	Возможно, нужен другой индекс или составной индекс

Сбор статистики (анатически)

Планировщик опирается на статистику — гистограммы распределения значений, количество NULL, количество уникальных значений в таблице. Если статистика устарела (после массового изменения данных), план может стать неоптимальным. Команда для обновления статистики (PostgreSQL):

ANALYZE users;

Правила:

Установите автовакуум с корректными порогами (autovacuum).
После массовых INSERT/UPDATE/DELETE (загрузка большого файла) делайте ручной ANALYZE.
Для очень больших таблиц можно увеличить статистику (default_statistics_target).

Пример оптимизации с EXPLAIN

Оригинальный запрос: выборка заказов пользователя за последний месяц.

SELECT * FROM orders WHERE user_id = 123 AND created_at > '2025-04-01';

План (до оптимизации):

Seq Scan on orders (cost=0.00..4500.00 rows=1000 width=100)
  Filter: ((user_id = 123) AND (created_at > '2025-04-01'))

Запрос делает Seq Scan на 1 миллион строк.
Время выполнения — 2 секунды.

Создаём индекс:

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

План после оптимизации:

Index Scan using idx_orders_user_created on orders (cost=0.29..12.50 rows=10 width=100)
  Index Cond: ((user_id = 123) AND (created_at > '2025-04-01'))

Количество оцениваемых строк упало с 1 миллиона до 10.
Время выполнения — 10 миллисекунд.

Вывод: без индекса база читала всю таблицу; с индексом — только нужные строки.

Особенности в разных СУБД

СУБД	Команда EXPLAIN	Особенности
PostgreSQL	`EXPLAIN (ANALYZE, BUFFERS, SETTINGS)`	Наиболее детальный. Показывает чтение из кэша (`buffers: shared hit=… read=…`)
MySQL	`EXPLAIN` → выдаёт план, но нет `ANALYZE` (вместо этого можно `EXPLAIN FORMAT=JSON` или `SHOW PROFILE`)	Не показывает реальное время выполнения. Анализ производительности через slow log.
SQLite	`EXPLAIN QUERY PLAN`	Упрощённый план. Полезен для проверки использования индексов.
Oracle	`EXPLAIN PLAN` + `SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)`	Очень мощный, много вариантов.

Что нужно знать аналитику

Вам не обязательно быть DBA, но полезно:

При жалобах на тормоза запросить у разработчика EXPLAIN (ANALYZE, BUFFERS) (для PostgreSQL) и посмотреть, есть ли Seq Scan на больших таблицах.
Понимать, что такое “покрывающий индекс” — если запрос использует Index Only Scan, это признак хорошей оптимизации.
Требовать от команды регулярного сбора статистики (ANALYZE) для таблиц, в которые часто пишут.
Уметь отличать “план изменился из-за изменения данных” от “никогда не было индекса”.

Аналитик может не строить планы, но он должен знать, что EXPLAIN — это основной инструмент разговора с БД, когда запросы начинают тормозить. Если вы можете сказать: “У нас на таблице заказов нет индекса по полю user_id, давайте создадим”, — вы сэкономите команде недели гаданий.

Резюме

EXPLAIN — команда получения плана выполнения SQL-запроса. Она показывает, каким образом база данных собирается читать данные, какие индексы использовать, в каком порядке соединять таблицы.

Ключевые узлы плана:

Seq Scan — полное сканирование таблицы (плохо для больших таблиц).
Index Scan / Index Only Scan — использование индекса (хорошо).
Bitmap Scan — комбинированный метод.
Nested Loop, Hash Join, Merge Join — стратегии соединения таблиц.
Sort — сортировка (дорого; избегайте, если не нужна).

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

Отсутствие индекса на поле WHERE/JOIN.
Устаревшая статистика (планщик не знает реальное распределение данных).
Непокрывающий индекс (запрос выбирает поля, которых нет в индексе).

EXPLAIN ANALYZE выполняет запрос и показывает реальное время и количество строк (осторожно на production!).

Оконные функции Лучшие практики