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

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

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

Как отвечать

Частичный индекс это индекс с условием WHERE: в него попадают только строки, для которых условие истинно. В PostgreSQL и SQLite он есть, в MySQL нет. Где он полезен:

• горячее подмножество: CREATE INDEX ON orders (created_at) WHERE status = 'pending'. Необработанных заказов тысячи, а всего миллионы, индекс маленький и держится в памяти;
• уникальность с мягким удалением: CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL. Email уникален среди живых пользователей, а удалённые не мешают повторной регистрации;
• редкие значения: индекс с WHERE error IS NOT NULL, когда ошибок доли процента;
• дешёвая запись: строки, не подходящие под условие, индекс вообще не обновляют.

Главное правило: планировщик берёт индекс, только если может доказать, что условие запроса влечёт условие индекса. pgx по умолчанию готовит и кэширует запросы, и при WHERE status = $1 PostgreSQL после нескольких выполнений может перейти на обобщённый план, где значение параметра неизвестно, и частичный индекс перестанет подходить. Поэтому условие индекса пишут в SQL литералом: WHERE status = 'pending' AND created_at < $1.

На живой таблице индекс создают через CREATE INDEX CONCURRENTLY. Внутри транзакции он не работает, поэтому в goose такую миграцию помечают -- +goose NO TRANSACTION.

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

Пишут условие запроса не так, как в индексе: индекс по status = 'pending', а запрос с status IN ('pending', 'new') или с параметром. Индекс есть, но не используется.

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

Забывают про ON CONFLICT: для частичного уникального индекса нужно повторить предикат, ON CONFLICT (email) WHERE deleted_at IS NULL, иначе PostgreSQL не найдёт подходящий индекс и вернёт ошибку.