Использовали ли частично покрывающие индексы

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

Знаете ли вы частичные индексы как инструмент и понимаете ли, почему из Java-кода они иногда перестают работать. Термин в вопросе размыт, поэтому интервьюер заодно смотрит, отличаете ли вы частичный индекс от покрывающего. Следом обычно просят показать EXPLAIN и объяснить, почему индекс не выбран.

Как отвечать

Сначала разведите понятия:

• частичный индекс: строится только по строкам, подходящим под условие. В PostgreSQL это CREATE INDEX ON orders (created_at) WHERE status = 'ACTIVE'. В SQL Server то же называется filtered index, в MySQL частичных индексов нет;
• покрывающий индекс: содержит все колонки запроса, и база отвечает из индекса без обращения к таблице. В PostgreSQL с версии 11 для этого есть INCLUDE (...), в плане видно Index Only Scan.

Типичные сценарии частичного индекса: очередь задач, где активных строк единицы процентов; уникальность при мягком удалении, CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL; выборки по редкому статусу.

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

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

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

Путают частичный и покрывающий индекс. Лучше сразу уточнить, о каком из них речь.

Проверяют запрос в консоли с литералом, видят индекс и не понимают, почему в приложении медленно. Причина в параметре и общем плане.

Ждут Index Only Scan сразу после загрузки данных. Пока VACUUM не обновил карту видимости, база всё равно ходит в таблицу, в плане это видно по Heap Fetches.