При каких условиях оптимизатор может применить фильтрованный индекс, если запрос логически выбирает только строки из его области?
Оптимизатор может применить фильтрованный индекс, только если во время построения плана способен доказать, что предикат запроса возвращает подмножество строк, включённых в предикат индекса. Логической эквивалентности результата недостаточно: доказательство должно быть доступно оптимизатору по форме условий, значениям параметров и типам данных.
Если предикат задан неизвестным параметром, записан через непрозрачное для оптимизатора выражение или не позволяет безопасно вывести такое включение, фильтрованный индекс может не использоваться даже при фактическом совпадении данных.
Обычный индекс содержит записи для всех строк таблицы, поэтому при большой доле редко используемых строк он расходует место и увеличивает стоимость обслуживания. Фильтрованные индексы в SQL Server и близкие по смыслу частичные индексы в PostgreSQL появились для индексации только важного подмножества данных.
Такой подход особенно полезен для статусов, архивных признаков, ненулевых значений и других условий, при которых индексируемая часть существенно меньше таблицы. Цена экономии — более сложная проверка применимости индекса к каждому запросу.
Предположим, в таблице много завершённых заказов и немного активных. Индекс только по активным заказам может быть компактным и быстрым, но он не является универсальным индексом по таблице.
Если запрос явно ограничивает результат активными заказами, применение индекса безопасно. Если же условие передано как параметр, оптимизатор может не знать его значение при компиляции и не сможет доказать, что результат не затрагивает строки за пределами фильтра.
Неверный выбор приводит к сканированию таблицы или другого индекса, лишним чтениям и росту задержки. Принудительное использование индекса без доказательства применимости опасно: оно может исключить нужные строки или привести к некорректному плану, поэтому СУБД не должна выбирать такой индекс небезопасно.
Рассуждение строится как проверка логического включения. Если фильтр индекса означает «строка имеет статус active», то предикат запроса должен гарантировать это условие, например дополнительно выбирать конкретного клиента или диапазон дат среди активных строк.
Минимальный пример для SQL Server:
Во втором запросе условие Status = 'Active' явно гарантирует принадлежность строки фильтрованному индексу. Индекс может использоваться для поиска по CustomerId и чтения CreatedAt, но окончательное решение зависит от оценки стоимости, статистики и альтернативных индексов.
Проблема возникает, когда запрос имеет вид «статус равен неизвестному параметру». При компиляции оптимизатору может быть нельзя доказать, что параметр всегда равен Active; фактическое значение станет известно только во время выполнения. В PostgreSQL аналогичная проблема возможна для подготовленных запросов с обобщённым планом.
На применимость также влияют преобразования типов, выражения над столбцом и форма предиката. Даже если человек легко видит логическое следствие, конкретный оптимизатор может не уметь вывести его из сложной записи.
Фильтрованный индекс уменьшает размер индекса и стоимость его обновления для строк, не входящих в фильтр. Однако строки, которые часто меняют состояние и переходят внутрь или наружу фильтра, могут вызывать дополнительные изменения индекса. Кроме того, такой индекс нельзя рассматривать как замену полному индексу для запросов, которым нужны остальные строки.
Обычно проверяют план на реальных формулировках запроса и актуальных статистиках. Если параметризация мешает доказательству, возможны варианты: специализированный план, перекомпиляция, изменение формы запроса или обычный индекс. Подсказка принудительного выбора — последний вариант, поскольку она связывает код с конкретным планом и может ухудшить поведение после изменения распределения данных.
В системе заказов 95 процентов строк имели статус Closed, а рабочие экраны почти всегда показывали только Active. Рассматривались три варианта: полный индекс по статусу, фильтрованный индекс и подсказка, принуждающая использовать существующий индекс.
Полный индекс был универсальнее, но занимал больше места и обслуживался при изменениях всех заказов. Подсказка давала контроль над планом, однако делала его хрупким при изменении распределения статусов и усложняла сопровождение.
Выбрали фильтрованный индекс для активных заказов, а запросы с фиксированным условием активного статуса оставили в форме, позволяющей оптимизатору доказать применимость фильтра. Для универсального поиска по любому статусу сохранили отдельный путь выполнения, поскольку фильтрованный индекс не покрывал этот сценарий.
В результате запросы рабочего экрана получили поиск по компактному индексу вместо чтения большей структуры. При этом операции, возвращавшие закрытые или все заказы, не стали ошибочно зависеть от фильтрованного индекса и продолжили выбирать планы среди подходящих полных индексов и сканирования.
Нет. Оптимизатор принимает решение до выполнения запроса и должен доказать корректность использования индекса для всех допустимых строк результата. Фактическое совпадение текущих данных не заменяет логического доказательства, иначе план мог бы стать некорректным после изменения данных.
Active может помешать использованию фильтрованного индекса?При параметре оптимизатор не всегда знает значение на этапе компиляции или строит план, общий для разных значений. Если он не может гарантировать, что параметр равен условию фильтра, использование индекса может быть небезопасным или недоказуемым. Возможные решения — специализированная компиляция, иной способ формирования запроса или обычный индекс, но выбирать их следует по измерениям.
Нет. Оптимизатор сравнивает стоимость всех вариантов. Если запрос возвращает большую часть строк фильтрованного подмножества, требует столбцов, которых нет в индексе, или имеет дорогие обращения к таблице, сканирование индекса либо другой план может оказаться выгоднее. Размер индекса — важное преимущество, но не гарантия лучшего плана.