Что такое частичные индексы и для чего они используются

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

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

Как отвечать

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

CREATE INDEX idx_orders_active ON orders (created_at)
WHERE status = 'ACTIVE';

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

Второй сильный сценарий это частичная уникальность. Правило «один активный договор на клиента» при мягком удалении выражается уникальным индексом с предикатом по неудалённым строкам, и обычным ограничением его не заменить.

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

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

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

Пишут предикат, который никогда не совпадёт с условием в коде: в индексе сравнение с одним значением, в запросе перечисление вариантов или сравнение с приведением типа.

Переносят синтаксис между СУБД вслепую. Предикат в определении индекса есть не везде: где-то он называется фильтрованным индексом, а где-то отсутствует и эмулируется индексом по выражению.