Краткий ответ

Наличие двух подходящих индексов не означает, что оптимизатор обязан объединить их результаты. Пересечение индексов требует дополнительных операций: чтения нескольких структур, сопоставления идентификаторов строк и, возможно, последующих обращений к таблице. Оптимизатор выбирает такой путь только тогда, когда его оценочная стоимость ниже стоимости одного индекса или полного сканирования.

Исторический контекст

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

Однако это не универсальная замена составному индексу. Комбинирование индексов полезно лишь тогда, когда уменьшение числа кандидатов компенсирует стоимость чтения и сопоставления нескольких наборов идентификаторов строк.

Постановка проблемы

Предположим, в таблице есть отдельные индексы по Статус и Регион, а запрос фильтрует оба столбца. Наивное ожидание состоит в том, что СУБД найдёт строки по каждому индексу и оставит их пересечение.

На практике один из индексов может быть малоселективным, второй — давать много идентификаторов строк, а пересечение этих наборов потребует значительной памяти и CPU. Если после этого всё равно придётся читать множество строк из таблицы, комбинированный план может оказаться медленнее одного индексного доступа или полного сканирования.

Подробное решение

При пересечении индексов оптимизатор обычно строит несколько потоков поиска, получает идентификаторы строк и сопоставляет их. В зависимости от СУБД это может быть реализация через bitmap-представление, хеширование, сортировку или другой внутренний оператор; конкретный алгоритм зависит от движка.

Стоимость оценивается по нескольким факторам:

селективность каждого предиката и ожидаемый размер промежуточных наборов;
стоимость чтения обоих индексов;
стоимость операции пересечения;
необходимость случайных обращений к таблице за остальными столбцами;
наличие статистики и точность оценки количества строк;
возможность получить результат из одного покрывающего или составного индекса.

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

Составной индекс часто эффективнее двух отдельных, когда порядок его ключей соответствует типичным условиям поиска. Но он увеличивает размер индекса, стоимость изменений данных и не покрывает произвольные комбинации предикатов. Поэтому решение принимают по реальным планам, распределению данных и нагрузке, а не только по числу условий в запросе.

Минимальный пример идеи:

CREATE INDEX IX_Orders_Status ON Orders(Status);
CREATE INDEX IX_Orders_Region ON Orders(Region);

SELECT OrderId, Amount
FROM Orders
WHERE Status = 'Open'
  AND Region = 'North';

СУБД может использовать один индекс, попытаться пересечь оба или выполнить сканирование. Сам факт существования двух индексов не фиксирует план: выбор определяется оценкой стоимости и доступными физическими операциями конкретной СУБД.

Ситуация из практики

В таблице заказов хранились сотни миллионов строк. Отдельный индекс по статусу возвращал значительную долю таблицы, а индекс по региону был существенно селективнее. Для запроса по открытому заказу из конкретного региона оптимизатор использовал индекс региона и проверял статус как остаточный фильтр.

Рассматривались три варианта. Пересечение двух индексов несло дополнительную стоимость чтения и сопоставления идентификаторов. Полное сканирование было устойчивым, но избыточным для редких регионов. Составной индекс по региону и статусу ускорял запрос, однако увеличивал стоимость вставок и обновлений.

Выбрали составной индекс после проверки нескольких наиболее частых запросов и измерения влияния на запись. Такой выбор был оправдан стабильной селективностью региона и высокой долей чтений; для менее частых запросов отдельное пересечение индексов не давало преимущества.

Что кандидаты часто упускают

Может ли пересечение двух индексов быть хуже использования только одного?

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

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

Почему обновление статистики может изменить решение о пересечении индексов?

Оптимизатор выбирает план на основе оценок, а не фактического числа строк во время построения плана. Статистика помогает оценить селективность каждого предиката и размер промежуточных результатов.

После загрузки данных распределение значений может измениться. Старые оценки способны сделать пересечение внешне дешёвым или, наоборот, скрыть его пользу. Обновление статистики может привести к другому плану даже без изменения индексов или текста запроса.

Когда составной индекс обычно предпочтительнее пересечения отдельных индексов?

Он обычно предпочтительнее, когда набор предикатов стабилен, порядок ключей подходит для поиска, а запросы часто выполняются на большой таблице. Один индекс позволяет эффективнее ограничить диапазон и иногда сразу вернуть нужные столбцы без дополнительных обращений к таблице.

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