EXPLAIN и оптимизация: вопросы для собеседования (часть 4)
EXPLAIN показывает план выполнения запроса и помогает понять, где теряется производительность. На собеседованиях спрашивают про индексы, Seq Scan vs Index Scan, стоимость JOIN-ов и как переписать медленный запрос. Этот навык отличает аналитика, который просто пишет SQL, от того, кто понимает, как база данных его выполняет.
Вопросы 16–20 из 20
16В таблице `orders` есть индекс по `status`, но `EXPLAIN` для условия `WHERE status IN ('paid','shipped')` показывает `Seq Scan`. Какое объяснение наиболее правдоподобно, если эти статусы встречаются у большинства заказов?
AПланировщик ожидает, что в выборку попадёт много строк, и `Seq Scan` будет дешевле множества случайных чтений по индексу
BИндекс на колонке всегда игнорируется при использовании оператора `IN` с двумя и более значениями в условии
CПоявление `Seq Scan` указывает на то, что таблица заблокирована другим запросом и индекс временно недоступен
DИспользование `Seq Scan` означает, что индекс физически повреждён и его нужно пересоздать перед следующим запросом
Ответ: Индекс не всегда выгоден: при выборке большой доли строк `Seq Scan` дешевле случайных обращений по индексу.
Индекс эффективен, когда условие `WHERE` сильно сужает выборку. Если же фильтр пропускает большую долю строк, чтение через индекс превращается в множество случайных обращений к таблице и оказывается дороже последовательного сканирования. В этом случае планировщик выбирает `Seq Scan`, и это нормальное поведение, а не признак проблем. Утверждения про `IN`, блокировки и повреждение индекса — типичные мифы, не соответствующие тому, как принимается решение по плану.
Подробный разбор → 17Есть индекс по `orders.created_at`, но `EXPLAIN` для фильтра `WHERE date(created_at) = current_date` показывает `Seq Scan`. Почему это часто происходит?
AИндекс «ломается» при любом использовании `WHERE` со столбцом `created_at` в условии фильтрации
BФункция `date(created_at)` меняет выражение, и индекс по `created_at` не может использоваться напрямую
C`EXPLAIN` всегда показывает `Seq Scan` для условий с равенством, даже когда индекс реально применяется
DУсловие `=` в `WHERE` отключает использование индексов в большинстве реляционных СУБД по умолчанию
Ответ: Индекс чаще используется, когда в `WHERE` сравнивается сам столбец, а не результат функции от него.
Когда вы пишете `date(created_at)`, база должна вычислить дату для каждой строки, и обычный индекс по `created_at` становится мало полезен. Часто запрос переписывают в виде диапазона по времени, например `created_at >= current_date AND created_at < current_date + interval '1 day'`. Тогда условие становится «индексируемым», и шанс увидеть `Index Scan` выше. Альтернатива — функциональный индекс `CREATE INDEX ... ON orders (date(created_at))`, но это лишние накладные расходы на запись.
Подробный разбор → 18Вы добавили индекс по `created_at`, ожидая ускорить `SELECT * FROM orders ORDER BY created_at DESC LIMIT 50`, но `EXPLAIN` всё равно показывает `Seq Scan` и `Sort`. Какой вывод самый безопасный?
AПланировщик считает текущий путь дешевле по оценкам `cost`: проверять селективность, статистику и форму запроса через `EXPLAIN`
BПланировщик `Postgres` игнорирует индекс по столбцу даты, если в запросе нет условия `WHERE` с фильтрацией строк
CИндекс по полю `created_at` применяется только при операциях записи, а на запросах чтения план идёт через `Seq Scan`
DНаличие `LIMIT 50` в запросе блокирует использование индексов и переключает план на чтение всей таблицы через `Seq Scan`
Ответ: То, что индекс не выбран, не означает, что он бесполезен — это решение планировщика в конкретных условиях.
Планировщик сравнивает альтернативы и выбирает то, что кажется дешевле по оценкам `cost`/`rows`. Индекс мог не подойти из‑за формы запроса, из‑за того, что нужно прочитать слишком много строк, или из‑за неактуальной статистики. `EXPLAIN` помогает увидеть факт: какой план выбран и где узлы `Sort`/`Seq Scan`, — а дальше уже искать причину и варианты переписывания запроса.
Подробный разбор → 19Вы ожидаете, что `LIMIT 10` всегда означает «обработать только 10 строк». Но `EXPLAIN` для запроса `SELECT * FROM events WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10` показывает `Sort` по большому количеству строк перед `Limit`. Что это означает?
A`LIMIT 10` игнорируется планировщиком, и база возвращает все строки таблицы независимо от ограничения в запросе
BБаза обязана сначала вернуть все строки клиенту, а потом приложение само применяет `LIMIT 10` уже на своей стороне
C`EXPLAIN` запрещено использовать вместе с `ORDER BY`, поэтому план показывает заведомо некорректную картину выполнения
DЧтобы выбрать первые строки в нужном порядке, базе может понадобиться обработать большой набор данных, если нет подходящего индекса
Ответ: `LIMIT` экономит работу только если план может рано остановиться, например через индекс под `ORDER BY`.
Когда план включает `Sort`, базе всё равно нужно рассмотреть большой набор строк, чтобы понять, какие из них первые по `ORDER BY`. В этом случае `LIMIT` лишь сокращает финальный вывод, но не избавляет от основной работы. Подходящий индекс по `(user_id, created_at DESC)` или более селективный фильтр позволяют плану рано остановиться и сделать `LIMIT` реально полезным. Утверждения про игнорирование `LIMIT`, обязательную передачу всех строк клиенту или запрет `EXPLAIN` с `ORDER BY` неверны.
Подробный разбор → 20Вы строите виджет «последние события» и используете `ORDER BY created_at DESC LIMIT 100`. В `created_at` много одинаковых значений, и пользователи жалуются, что список «дрожит». Какое изменение лучше всего повышает стабильность результата, не ломая идею оптимизации через индекс?
AУбрать `ORDER BY` и сортировать результат на стороне приложения, чтобы база не тратила ресурсы на упорядочивание строк.
BЗаменить `LIMIT 100` на `FETCH FIRST 100 ROWS WITH TIES`, чтобы при совпадении значений возвращались все связанные строки.
CДобавить `ORDER BY random()` как вторичный ключ, чтобы каждый запрос возвращал непредсказуемо случайный, но стабильный для пользователя порядок.
DСделать порядок детерминированным, например `ORDER BY created_at DESC, event_id DESC LIMIT 100` (и при необходимости иметь индекс, который поддерживает такой порядок).
Ответ: Для стабильного `LIMIT` нужен детерминированный `ORDER BY` с tie-breaker.
Если сортировка идёт только по `created_at`, то строки с одинаковым временем могут возвращаться в разном порядке в разных запусках — SQL это не запрещает. Добавление второго ключа (например, `event_id`) делает порядок однозначным. При хорошем индексе по `(created_at, event_id)` или похожем порядке оптимизация через чтение из индекса может сохраниться.
Подробный разбор →