Работает ли составной индекс, если в запросе используются только первые колонки индекса?

Что проверяют

Знаете ли вы правило левого префикса и умеете ли им пользоваться, а не только пересказывать. Для бэкендера на Java вопрос почти всегда практический: запрос генерирует ORM, и попадает он в индекс или нет, выясняется по плану, а не по ощущениям.

Как отвечать

Составной индекс это упорядоченный список ключей: сначала по первой колонке, внутри неё по второй и так далее. Отсюда правило: индекс по трём колонкам помогает поиску по первой, по первой и второй, по всем трём, и не даёт быстрого поиска по одной второй или третьей.

create index idx_orders on orders (user_id, status, created_at);

select * from orders where user_id = ? and status = ?; -- попадает
select * from orders where status = ?;                 -- префикс разорван

Важная деталь, которую редко называют: префикс разрывает не только пропущенная колонка, но и диапазон. В запросе с условиями user_id = ?, status > ? и created_at = ? по ключу ищутся только первые две колонки, третья превращается в фильтр по найденным строкам. Поэтому равенства ставят в начало индекса, а диапазон и сортировку в конец.

Префикс даёт ещё и порядок: сортировка по первым колонкам индекса идёт без отдельной сортировки, по средней колонке нет. Если все нужные колонки лежат в индексе, запрос закрывается им целиком, без чтения таблицы.

Проверять всё это надо планом: explain analyze и включённый вывод реального SQL, потому что Hibernate легко превращает вашу мысль в другой запрос.

Где спотыкаются

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

Второе: заворачивают колонку в функцию или сравнивают с другим типом. Условие вида where lower(email) = ? и неявное приведение типа обнуляют индекс, даже когда колонка стоит первой.

Третье: пишут метод репозитория и считают, что порядок полей в его имени что-то значит для базы. Значение имеет порядок колонок в индексе; сигнатура метода на план запроса не влияет.