Stage 10. EXPLAIN и оптимизация запросов

Запрос может быть быстрым на разработке и внезапно стать медленным в production. На 1 000 строк даже слабый запрос часто выполняется мгновенно. На 1 000 000 строк тот же запрос уже может занимать секунды. Иногда индексы вроде есть, но база все равно не использует тот индекс, который вы ожидали.

Главное правило простое: мы не знаем, что реально делает база данных, пока не посмотрим план выполнения. Оптимизацию запроса нужно начинать с фактов, а не с догадок.

Что показывает EXPLAIN

EXPLAIN показывает план, который база собирается использовать для выполнения запроса. В PostgreSQL план может показать тип сканирования, стратегию join, сортировку, ожидаемое количество строк и оценочную стоимость. Думайте об этом как о “рентгене” SQL: текст запроса показывает, что вы написали, а план показывает, как база реально будет это выполнять.

Есть две частые формы:

EXPLAIN
SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN оценивает план, но не выполняет сам запрос.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN ANALYZE выполняет запрос и показывает реальное время выполнения и фактическое количество строк. С тяжелыми UPDATE, DELETE и INSERT его нужно использовать осторожно, потому что statement реально выполнится, если вы не обернули его в транзакцию и не сделали rollback.

Как читать план без перегруза

Новичку не нужно сразу понимать каждый узел плана. Начните с четырех сигналов.

Что в плане	Что это значит	Хорошо или плохо
Seq Scan	Полный перебор таблицы	Плохо на большой таблице, если должен быть селективный индекс
Index Scan	База использует индекс для поиска строк	Обычно хорошо для селективных фильтров
Rows	Сколько строк база ожидает или реально читает	Важно для понимания селективности
Cost / Actual time	Оценочная работа против реального времени	Показывает, где проблема

Часто самый полезный сигнал — сравнение ожидаемых строк и фактических строк. Если PostgreSQL ожидал 100 строк, а нашел 500 000, оптимизатор мог выбрать плохой план из-за неверной статистики или неверных предположений.

Главный анти-паттерн: Seq Scan на большой таблице

Допустим, в users лежит 5 миллионов строк, а приложение ищет пользователя по email:

EXPLAIN ANALYZE
SELECT id, email, name
FROM users
WHERE email = '[email protected]';

Если в плане написано Seq Scan on users, PostgreSQL читает таблицу строка за строкой, пока не найдет подходящие данные. На большой таблице это обычно тот самый момент “ага”: запрос медленный не потому, что SQL загадочный, а потому что у базы нет быстрого пути к нужной строке.

Обычное решение — точечный индекс:

CREATE INDEX idx_users_email ON users(email);

После этого снова смотрим план:

EXPLAIN ANALYZE
SELECT id, email, name
FROM users
WHERE email = '[email protected]';

План должен перейти от Seq Scan к Index Scan или другому варианту доступа через индекс. Пример результата:

Состояние	План	Время
До индекса	Seq Scan	2.3s
После индекса	Index Scan	12ms

Точные цифры зависят от железа, кэша, размера данных и селективности, но процесс всегда одинаковый: меняем одну вещь, снова измеряем.

Когда индекс не помогает

Индексы не магия. Запрос может не использовать индекс или использовать его неэффективно.

Частая причина — функция вокруг индексируемой колонки:

WHERE LOWER(email) = '[email protected]'

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

CREATE INDEX idx_users_lower_email ON users (LOWER(email));

Еще одна причина — неправильный порядок колонок в составном индексе. Индекс (status, created_at) полезен для фильтра по status и сортировки по created_at, но может слабо помочь запросу, который фильтрует только по created_at.

Селективность тоже важна. Если условие подходит почти всей таблице, индекс может быть медленнее полного сканирования. Например, status = 'ACTIVE' не селективен, если 95% пользователей активны. В таком случае база может правильно выбрать full scan, потому что читать почти всю таблицу через индекс дороже.

Оптимизация через понимание, а не угадывание

Используйте повторяемый цикл:

1. Найти медленный запрос.
2. Запустить EXPLAIN ANALYZE.
3. Найти дорогой узел или неверную оценку.
4. Изменить одну вещь: индекс, форму запроса, фильтр, выбранные колонки или статистику.
5. Снова запустить EXPLAIN ANALYZE.
6. Сравнить “до” и “после”.

Не меняйте пять вещей сразу. Если станет быстрее, вы не поймете, что помогло. Если станет хуже, вы не поймете, что сломало план.

Частые сигналы в плане

Некоторые узлы плана не являются автоматически плохими, но это важные сигналы.

Сигнал в плане	Что может означать
Nested Loop на больших входах	Join может много раз сканировать большое количество строк
Hash Join	База строит hash-таблицу для соединения; часто нормально для больших equality join
Merge Join	Входные данные отсортированы и соединяются по порядку; полезно, когда подходят сортировки или индексы
Sort	База должна отсортировать строки; на большом результате это дорого
Sort уходит на диск	Памяти для сортировки не хватило, поэтому используется диск и запрос замедляется
Bitmap Index Scan	База сначала собирает совпадения по индексу, потом читает строки таблицы; часто встречается при фильтрах средней селективности

Цель не в том, чтобы выучить все узлы наизусть. Цель — задавать правильные вопросы: почему таблица сканируется полностью, почему читается так много строк, почему есть большая сортировка и почему оценки сильно отличаются от факта.

Мини-чеклист оптимизации

Перед изменением SQL или добавлением индекса проверьте:

• есть ли Seq Scan на большой таблице;
• используется ли нужный индекс;
• нет ли функций вокруг индексируемых колонок в WHERE;
• достаточно ли селективно условие;
• правильный ли порядок колонок в составном индексе;
• выбираете ли вы только нужные приложению колонки;
• сравнили ли вы EXPLAIN ANALYZE до и после.

Практика перед следующим уроком

Попробуйте руками повторить примеры из статьи на маленьком наборе данных из 5-10 строк. Это важный шаг: когда вы сами запускаете SQL и видите конкретный результат таблицей, материал перестает быть абстрактным. Сначала выполните запрос без оптимизаций, затем внесите одно изменение и посмотрите, как меняется результат или план выполнения. Если что-то не сходится, зафиксируйте вопрос и проверьте: корректны ли фильтры, правильно ли выбраны поля, не потерялись ли строки из-за условий соединения, не появился ли NULL там, где вы его не ожидали.

Мини-проверка понимания:

1. Могу объяснить тему урока одним простым предложением.
2. Могу написать базовый SQL-пример без подсказки.
3. Могу прочитать результат запроса и объяснить каждую колонку.
4. Могу назвать минимум одну частую ошибку и как ее избежать.

Если работаете в команде, сохраняйте измерения “до/после” рядом с изменением запроса в описании pull request. Так появляется общая история оптимизации, а не повторяющиеся эксперименты наугад.

Дополнительный сценарий: проверка после релиза

После релиза нового фильтра или сортировки полезно сразу перепроверить 2-3 ключевых запроса через EXPLAIN. Даже небольшое изменение в условии может внезапно перевести запрос из index scan в последовательное сканирование. Регулярная проверка после релиза помогает поймать такие регрессии до жалоб пользователей.

Главный вывод: профессионал не оптимизирует запросы “на глаз”. Он читает, что делает база данных. EXPLAIN — это переход от догадок к фактам.