Частичный индекс PostgreSQL не выбран: сравните предикат запроса

Таблица содержит миллионы завершённых заказов и небольшое число неоплаченных. Для страницы неоплаченных заказов создан компактный частичный индекс. Однако другой вариант того же запроса снова читает много строк. Индекс существует, но наличие объекта ещё не означает применимость ко всем формам фильтра.

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

Как изменить решение

CREATE INDEX orders_unpaid_created_idx
ON orders (created_at, id)
WHERE paid_at IS NULL;

EXPLAIN
SELECT id, created_at FROM orders
WHERE paid_at IS NULL
ORDER BY created_at, id
LIMIT 100;

В запросе явно повторено paid_at IS NULL. Индекс содержит нужное подмножество и поддерживает порядок страницы. Фильтр status = awaiting_payment сам по себе не доказывает paid_at IS NULL, даже если приложение обычно хранит эти состояния согласованно.

Согласуйте одно выражение бизнес-состояния в схеме и запросах. Дублирующие флаги paid, status и paid_at усложняют не только индексы, но и правильность отчётов. Если нужна проверка связи между ними, она должна быть явно обеспечена, а не существовать лишь в соглашении команды.

Что проверить на своих данных

Сравните EXPLAIN для точного условия и соседнего фильтра. Для реальных измерений выполните ANALYZE таблицы и проверьте разные размеры подмножества. На крошечной тестовой таблице последовательное чтение может быть дешевле, поэтому отсутствие Index Scan там не опровергает устройство индекса.

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

Практика дальше

Следующая практика — запросы, где правильность зависит от нескольких источников и граничных случаев. В курсе «Продвинутый SQL» вы решаете 109 задач на JOIN, окна и CTE, сверяете отчёты и собираете витрину интернет-магазина. Запросы выполняются в браузере на SQLite с автоматической проверкой.

Посмотреть программу и практику курса

Источник: Частичный индекс PostgreSQL не выбран — официальная документация.

Другие задачи по теме: PostgreSQL: запись, блокировки и планы запросов.