Почему составной индекс не используется при поиске по третьему столбцу без первых двух
Что проверяют
Знаете ли, как составной индекс устроен физически, и не путаете ли порядок столбцов в индексе с порядком условий в WHERE. Для бэкендера на Java это ещё и проверка, читаете ли вы планы запросов, которые генерирует ORM за вас.
Как отвечать
Составной индекс это дерево, ключ которого кортеж, отсортированный по порядку столбцов: сначала по первому, внутри равных значений по второму и так далее. Ровно как справочник по фамилии, имени, отчеству: найти всех Ивановых легко, найти всех, у кого отчество Петрович, можно только перебрав справочник целиком.
Отсюда правило левого префикса: индекс помогает искать, если заданы столбцы подряд с начала. Условие только по третьему столбцу не даёт точки входа, потому что подходящие записи разбросаны по всему дереву.
Что произойдёт на деле, зависит от базы. Оптимизатор может выбрать полное сканирование таблицы, а может прочитать индекс целиком, если тот покрывает все нужные столбцы: читать узкий индекс дешевле, чем широкую таблицу, хотя это всё равно не поиск. Некоторые движки умеют пропускающее сканирование, когда у первого столбца мало разных значений: они подставляют их по очереди и делают серию обычных поисков. Рассчитывать на это не стоит, но упомянуть полезно.
Лечение: либо отдельный индекс по третьему столбцу, либо новый составной, где он стоит первым. Порядок выбирают по тому, какие условия приходят вместе, и столбцы с равенством ставят перед столбцом с диапазоном: после диапазона следующие столбцы уже только фильтруют, а не сужают поиск.
Где спотыкаются
Переставляют условия в WHERE и ждут, что план изменится. Оптимизатор сам решает, в каком порядке применять предикаты, значение имеет только порядок столбцов в индексе.
Убивают индекс функцией или приведением типа: where lower(email) = ? или сравнение строкового столбца с числом. Дерево отсортировано по исходным значениям, по вычисленным искать нечем.
Третье: спорят словами вместо EXPLAIN. Для запроса, собранного Hibernate, сначала включают логирование готового SQL с параметрами, потом смотрят план на данных, похожих на боевые. На пустой тестовой базе сканирование всегда дешевле индекса, и вывод получится обратный.