sottoСначала измерить
Оптимизация начинается не с добавления индекса, а с фактов: какой запрос медленный, на каких параметрах, сколько строк читает, сколько возвращает и как часто вызывается. Часто проблема не в одном тяжёлом запросе, а в частом среднем (привет, N+1).
EXPLAIN показывает план, выбранный планировщиком; EXPLAIN ANALYZE во многих СУБД ещё и реально выполняет запрос, давая фактические время и строки. ANALYZE на write-запросах применяйте осторожно — он действительно изменит данные (или оберните в транзакцию с rollback).
Как читать план
В плане смотрят тип доступа (seq scan против index scan/index-only scan), порядок соединений и алгоритм join, оценку строк и стоимость. Большой разрыв между estimated и actual rows — признак устаревшей статистики или неудачной оценки селективности.
Seq scan не всегда плох: на маленькой таблице или при низкой селективности он дешевле индекса.
Порядок колонок в индексе
Составной B-tree использует левый префикс, поэтому порядок колонок важен. Частая стратегия: сначала колонки с равенством, затем диапазон/сортировка — тогда один индекс закрывает и фильтр, и ORDER BY.
Индекс (user_id, created_at desc) обслуживает запрос «последние заказы пользователя» без отдельной сортировки.
create index idx_orders_user_created
on orders (user_id, created_at desc);Пагинация: OFFSET против keyset
OFFSET на больших страницах дорог: база проходит и отбрасывает все предыдущие строки, поэтому стоимость растёт со страницей.
Keyset (seek) pagination запоминает последнее увиденное значение и продолжает по индексу — стоимость почти постоянна и не зависит от глубины.
-- keyset: дешевле OFFSET на глубоких страницах
select * from orders
where user_id = ?
and created_at < ? -- курсор: последнее значение прошлой страницы
order by created_at desc
limit 50;Статистика и селективность
Планировщик выбирает план не по самому запросу, а по СТАТИСТИКЕ: сколько строк в таблице, как распределены значения колонок, сколько среди них уникальных, какие значения встречаются чаще всего. По ней он оценивает селективность условия — какую долю строк оно оставит.
Отсюда правило чтения плана: сравнивайте оценку с фактом (rows=1000 против actual rows=900000). Расхождение на порядки означает, что планировщик обманут, и дальше он делает неверный выбор — берёт nested loop там, где нужен hash join, или игнорирует индекс.
Типичные причины расхождения: устаревшая статистика после массовой заливки данных (лечится ANALYZE), коррелированные условия (город и страна не независимы, а планировщик перемножает вероятности как независимые), выражения, по которым статистики просто нет.
Практический вывод: если план внезапно «испортился» после большой загрузки данных, первым делом обновите статистику, а не добавляйте индексы.
Что мешает индексу
Функция над колонкой (lower(email) = ?) мешает индексу — нужен функциональный индекс или нормализованное поле. SELECT * раздувает IO и лишает шанса на index-only scan. Ведущий wildcard в LIKE '%x' не использует B-tree. OR по разным колонкам часто хуже UNION.
Сюда же — неявное приведение типов: сравнение varchar-колонки с числом или timestamp с datetime может заставить БД привести колонку к другому типу, а это та же функция над колонкой со всеми последствиями.
И низкая селективность: индекс по колонке status с тремя значениями сам по себе почти бесполезен — база справедливо решит, что дешевле прочитать таблицу. Такие колонки имеют смысл как ВТОРАЯ часть составного индекса или в частичном индексе (WHERE status = 'ACTIVE') под конкретный горячий запрос.
-- индекс по email не сработает: функция над колонкой
select * from users where lower(email) = ?;
-- вариант 1: функциональный индекс
create index idx_users_email_lower on users (lower(email));
-- вариант 2: частичный индекс под горячий запрос
create index idx_orders_active on orders (created_at desc)
where status = 'ACTIVE';Как отвечать на собеседовании
Главное — показать ПОРЯДОК действий, а не список трюков: измерить и найти конкретный медленный запрос → посмотреть план через EXPLAIN (ANALYZE) → понять, где уходит время → подобрать индекс или переписать запрос → перепроверить планом. Ответ «добавлю индекс» без этой рамки звучит слабо.
Отдельно отметьте, что дело не всегда в одном тяжёлом запросе: часто виноват средний по стоимости, но вызываемый тысячи раз (N+1). Это сразу показывает, что вы смотрите на нагрузку целиком.
При чтении плана называйте, на что смотрите: тип доступа (seq scan против index scan / index-only scan), алгоритм соединения, и главное — расхождение estimated и actual rows как признак устаревшей статистики. Оговоритесь, что seq scan не всегда зло: на маленькой таблице или при низкой селективности он дешевле индекса.
Про индексы дайте одно сильное правило вместо перечисления: в составном индексе работает левый префикс, поэтому сначала колонки с равенством, потом диапазон и сортировка — тогда один индекс закрывает и фильтр, и ORDER BY.
Завершите пагинацией: OFFSET дорожает с глубиной страницы, потому что база проходит и отбрасывает все предыдущие строки, а keyset-пагинация продолжает по индексу от последнего значения и стоит почти одинаково на любой странице.