В чем разница частичного индекса и обычного индекса

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

Знаете ли вы, что индекс можно строить не по всей таблице, и понимаете ли, когда база его реально применит. Следом обычно спрашивают, как это выглядит в JPA-проекте и почему индекс есть, а план запроса его не берёт.

Как отвечать

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

CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'PENDING';

Что это даёт:

• размер: если «живых» строк 2% от таблицы, индекс в разы меньше, лучше помещается в память и дешевле обслуживается;
• запись: строки вне условия индекс не обновляют, вставка архивных данных его не трогает;
• уникальность по условию: CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL запрещает дубли только среди неудалённых. Это стандартный приём для мягкого удаления, обычным UNIQUE его не сделать.

Главное ограничение: планировщик использует частичный индекс, только если может доказать, что условие запроса влечёт условие индекса. Запрос без фильтра по status или с status IN ('PENDING', 'NEW') его не возьмёт.

Для Java здесь есть своя деталь. Если значение приходит параметром (status = ?), PgJDBC после нескольких выполнений переводит запрос на серверный prepared statement, и PostgreSQL может перейти на общий план, где значение параметра неизвестно, а частичный индекс уже не подходит. Поэтому литерал из условия индекса лучше писать в запросе прямо, например в @Query репозитория. И ещё: аннотация @Index в JPA условие не выражает, такие индексы создают миграцией Flyway или Liquibase.

PostgreSQL и SQLite умеют частичные индексы, в SQL Server это filtered index, в MySQL их нет.

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

Пишут в запросе условие, похожее по смыслу, но не совпадающее с предикатом индекса, например is_active IS NOT FALSE вместо is_active = true, и не понимают, откуда полное сканирование.

Считают, что частичный уникальный индекс защищает от дублей во всей таблице. Строки вне условия он не проверяет вообще.

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