Оптимизация SQL-запросов: индексы и план

B-tree: почему поиск занимает три шага

B-tree - дерево, в узлах которого лежат отсортированные ключи, а в листьях ссылки на строки таблицы. Каждый узел содержит не одно значение, а сотни, и делит диапазон на сотни частей.

Отсюда и скорость. На каждом спуске отбрасывается почти всё: узел на 256 ключей за один шаг сужает поиск в 256 раз. Три уровня накрывают миллионы строк, поэтому до нужного места добираешься за три-четыре обращения к диску вместо миллиона.

Второе полезное свойство спрятано в листьях: они связаны в цепочку по порядку. Найдя первое значение диапазона, база просто идёт по цепочке дальше и отдаёт все заказы между двумя датами подряд, без возврата к корню. Поэтому B-tree одинаково хорош и для точного равенства, и для диапазона.

// Составной индекс по (a, b) - это дерево, отсортированное сначала по a, потом внутри одинаковых a по b. Он работает для фильтра по a и для фильтра по a вместе с b, но по одному b - нет: значения b разбросаны по всему дереву. Порядок колонок в индексе - не косметика.

Что выключает индекс

Индекс хранит значения самой колонки. Как только колонка оказывается внутри функции, дерево становится бесполезным: в нём лежат значения created_at, а сравнить нужно значения date(created_at), и таких в индексе просто нет. База сдаётся и читает таблицу целиком.

Лечится двумя способами. Переписать условие диапазоном - created_at >= '2026-01-01' AND created_at < '2026-01-02' вместо date(created_at) = '2026-01-01'; результат тот же, а индекс работает. Или построить функциональный индекс, то есть индекс по самому выражению date(created_at) - тогда в дереве окажутся ровно те значения, которые сравниваются.

Тот же механизм объясняет поведение поиска по строке. LIKE 'abc%' индексом пользуется: известно начало, а дерево отсортировано именно с начала строки. LIKE '%abc' - нет: в дереве нет порядка по хвостам, искать негде. Для поиска по подстроке нужен другой инструмент - триграммный индекс или индекс по перевёрнутой строке.

// Есть и обратная ситуация, которую принимают за поломку. Если под фильтр попадает заметная часть таблицы, планировщик сознательно откажется от индекса и прочитает всё подряд - последовательное чтение половины таблицы дешевле, чем полмиллиона прыжков по ссылкам. На колонке с тремя разными значениями индекс не поможет никогда.

селективность

какая доля строк проходит фильтр. Чем меньше, тем полезнее индекс; на неселективной колонке он лишний

Как читать план запроса

EXPLAIN показывает, что база СОБИРАЕТСЯ делать, EXPLAIN ANALYZE - что она реально сделала и за сколько. Читается снизу вверх и изнутри наружу: самые вложенные узлы выполняются первыми.

Красных флагов три, и все узнаются с одного взгляда. Seq scan (последовательное чтение таблицы целиком) на большой таблице под узким фильтром - индекса нет или он выключен. Nested loop на миллионах строк - для каждой строки одной таблицы база пробегает вторую, и это квадратичная работа. И расхождение между rows estimated и actual в разы: планировщик считал по устаревшей статистике и выбрал план под несуществующие данные, лечится командой ANALYZE.

Покрывающий индекс - тот, в котором есть все колонки запроса, в ключе или в INCLUDE. Тогда база отвечает прямо из индекса, ни разу не заглянув в таблицу; в плане это видно как index-only scan. Привычка писать SELECT * ломает это начисто: тянутся все колонки, а в индексе их нет.

// И про замеры. Первый прогон греет кеш, поэтому сравнивать надо времена повторных прогонов, а решения принимать по actual rows в плане, а не по ощущению «вроде быстрее стало».

Пагинация, которая не деградирует

OFFSET 100000 звучит как «начни со стотысячной строки», а работает как «прочитай сто тысяч строк и выброси их». Первая страница летает, сотая ползёт, тысячная кладёт базу - деградация линейная по номеру страницы.

Keyset-пагинация решает это сменой вопроса. Вместо «дай сто тысяч первых и отбрось» спрашиваем «дай следующие двадцать после вот этого ключа»: WHERE id > last_seen_id ORDER BY id LIMIT 20. Индекс сразу прыгает в нужное место дерева, и сотая страница стоит ровно столько же, сколько первая.

// Цена честная: нельзя перепрыгнуть на страницу номер 500, можно только идти вперёд и назад. Для бесконечной ленты и выгрузок это ровно то, что нужно; для нумерованной пагинации в админке - нет. И индексы вообще не бесплатны: каждый замедляет вставку и обновление и занимает место, поэтому их ставят под реальные запросы, а не на всякий случай.

keyset-пагинация

страница «после последнего увиденного ключа» вместо счёта от начала. Её же зовут курсорной

Как отвечать: «Запрос фильтрует по индексированной дате, но тормозит. Почему?»

Иду по причинам в порядке частоты. Первое - функция вокруг колонки: условие вида date(created_at) = вчера индексом не пользуется, потому что в индексе лежат значения самой колонки, а не результат функции. Переписываю диапазоном, от полуночи до полуночи. Второе - смотрю EXPLAIN ANALYZE: если оценка строк расходится с фактической в разы, статистика устарела и план выбран под несуществующие данные, лечится командой ANALYZE. Третье - селективность: если под фильтр попадает заметная доля таблицы, планировщик сознательно уходит в последовательное чтение, и он прав, индекс тут не поможет. Тогда вопрос уже не к индексу, а к объёму выборки, и решается либо сужением фильтра, либо покрывающим индексом.

Три реальные причины в порядке частоты, к каждой - способ проверки и лечение. Это диагностический маршрут вместо гадания «наверное, индекс сломался».

На чём валят

• −Функция вокруг индексированной колонки в WHERE: индекс выключился молча, ошибки нет.
• −SELECT * на широкой таблице - сломан index-only scan, читаются лишние байты.
• −«Добавим индекс» на колонку с тремя значениями: планировщик его проигнорирует, а записи станут медленнее.
• −Глубокая пагинация через OFFSET: сотая страница читает и выбрасывает всё, что было до неё.
• −Мерить по первому прогону и не смотреть actual rows в плане: кеш и статистика останутся за кадром.