Как удобнее интерпретировать дерево EXPLAIN, чтобы понять, откуда берутся строки и где тратятся ресурсы запроса?
EXPLAIN показывает план выполнения запроса и помогает понять, где теряется производительность. На собеседованиях спрашивают про индексы, Seq Scan vs Index Scan, стоимость JOIN-ов и как переписать медленный запрос. Этот навык отличает аналитика, который просто пишет SQL, от того, кто понимает, как база данных его выполняет.
Всего в этом разделе 20 вопросов. Каждый — с правильным ответом и кратким разбором теории. Разбито на 4 части по 5 вопросов.
1Чем `EXPLAIN` отличается от `EXPLAIN ANALYZE` в `Postgres`-подобной СУБД?
A`EXPLAIN` физически выполняет запрос и показывает фактические времена шагов, а `EXPLAIN ANALYZE` строит только план без выполнения
B`EXPLAIN` показывает план без выполнения запроса, а `EXPLAIN ANALYZE` выполняет запрос и добавляет фактические времена и количество строк
CОба варианта строят только план: ни `EXPLAIN`, ни `EXPLAIN ANALYZE` не запускают запрос на исполнение и не возвращают замеров
DОба варианта всегда исполняют запрос: `EXPLAIN` и `EXPLAIN ANALYZE` отличаются только форматом вывода полученного плана
Ответ: `EXPLAIN` — план, `EXPLAIN ANALYZE` — план плюс фактические измерения по запросу.
Обычный `EXPLAIN` полезен, когда вы хотите оценить риск и структуру плана, не запуская потенциально тяжёлый запрос. `EXPLAIN ANALYZE` действительно выполняет запрос и показывает фактические времена и количество строк на шагах. Это помогает обнаружить расхождения между оценкой оптимизатора и реальностью, но может быть дорого на больших таблицах. Поэтому утверждения, что оба варианта только строят план или, наоборот, оба всегда исполняют запрос — неверны.
2Как удобнее интерпретировать дерево `EXPLAIN`, чтобы понять, откуда берутся строки и где тратятся ресурсы запроса?
AСверху вниз: сначала смотрят корневой узел, а нижние шаги уже разбирают по ходу обработки выдачи
BСлева направо: порядок выполнения зависит от ширины и положения узлов в текстовом представлении плана
CСнизу вверх: сначала идут узлы сканирования и фильтрации, а их результаты поднимаются к верхним узлам
DВ любом направлении: дерево показывает шаги в случайном порядке, поэтому важна только итоговая стоимость
Ответ: Чаще всего план читают снизу вверх: сканы → обработка → итоговый результат запроса.
В плане нижние узлы производят данные (например, `Seq Scan` или `Index Scan`). Дальше потоки обрабатываются: применяются условия, агрегации (`Aggregate`), сортировки (`Sort`), а в конце обрезка (`Limit`). Такой подход помогает связать узкие места с источниками большого объёма строк и оценить вклад каждого шага.
3Вы хотите добавить новый запрос в дашборд и боитесь, что он сильно нагрузит базу, потому что таблица `events` очень большая. Что даёт запуск `EXPLAIN` для этого запроса?
AОн выполнит запрос полностью и вернёт точное время выполнения в миллисекундах, как делает `EXPLAIN ANALYZE` на тестовой среде разработки
BОн покажет предполагаемый план выполнения, например `Seq Scan` или `Index Scan`, без фактического выполнения запроса на больших данных
CОн автоматически создаст подходящие индексы под ваш запрос, чтобы ускорить выборку строк из большой таблицы и снять часть нагрузки
DОн временно заблокирует таблицу `events` от записи, чтобы получить стабильный и воспроизводимый результат измерений на проде
Ответ: `EXPLAIN` показывает план выполнения и помогает оценить риск тяжёлого запроса без его запуска.
В Postgres-подобных СУБД `EXPLAIN` отображает выбранные шаги плана — например, `Seq Scan`, `Index Scan`, `Sort`, операции для `JOIN` — и их оценки. Это полезно, чтобы увидеть потенциальные узкие места до запуска запроса в проде. В отличие от `EXPLAIN ANALYZE`, обычный `EXPLAIN` не выполняет запрос.
4Вы считаете число заказов по таблице `orders`, добавили `JOIN` к таблице `order_items` и получили подозрительно большой результат. В `EXPLAIN` оценка числа строк после соединения сильно выше входной. Что это чаще всего означает?
A`EXPLAIN` сообщает точное время выполнения, и рост `rows` ведёт к росту отклика в той же пропорции в реальной системе
BСвязь один-ко-многим размножает строки в `JOIN`, и `COUNT(*)` считает позиции вместо заказов; нужен `COUNT(DISTINCT orders.id)`
CИндекс на ключе соединения отсутствует, без индекса `COUNT(*)` теряет корректность и результат восстановить нельзя
DБаза «потеряла» первичный ключ таблицы `orders`, и план оптимизатора перестаёт учитывать уникальность строк по ключу
Ответ: План помогает заметить размножение строк из-за `JOIN` и связать это с корректностью агрегатной метрики.
Если после `JOIN` ожидаемое число строк резко растёт, это часто нормальное следствие отношения один-ко-многим. Для аналитики важно, что агрегат может начать считать не то — например, позиции вместо заказов. `EXPLAIN` сам по себе не доказывает корректность, но подсказывает место, где возникает кратность и где стоит пересмотреть агрегацию или применить `COUNT(DISTINCT)`. Гипотезы про «отсутствие индекса» или «потерю первичного ключа» — частые мифы, не объясняющие наблюдение.
5В `EXPLAIN` вы видите оценку `rows=10`, но по смыслу запроса ожидаете тысячи строк. Какое объяснение и следующий шаг наиболее разумны?
AВозможна ошибка оценок из-за устаревшей статистики; стоит проверить реальную кардинальность через `EXPLAIN ANALYZE` и обновить статистику командой `ANALYZE`
BСтатистика корректна, оценка `rows=10` означает слишком селективный индекс; нужно добавить составной индекс на колонки фильтра и перезапустить запрос
C`JOIN` отфильтровал лишние строки из-за несовпадения типов; следует привести типы ключей к одному и проверить план через `EXPLAIN` повторно
D`rows` отражает количество строк после применения `LIMIT`; нужно убрать `LIMIT` из запроса, чтобы планировщик показал реальный объём данных
Ответ: Сильные расхождения в `rows` — повод подозревать неверные оценки и возможный плохой план.
`rows` в `EXPLAIN` — это прогноз планировщика. Если он сильно не совпадает с реальностью, оптимизатор может выбрать неправильный тип скана или порядок соединений. Для аналитика полезно: (1) подтвердить факт через `EXPLAIN ANALYZE` на безопасном окружении и (2) договориться об обновлении статистики (`ANALYZE`) или переписать условия так, чтобы оценки стали точнее.