Частичные индексы PostgreSQL: типичные ошибки и их цена

В PostgreSQL 16 частичный индекс по заказам со статусом «open» может ускорить выборку активных заказов и не занимать место для закрытых. Но стоит запросу фильтровать данные чуть иначе или планировщику не суметь доказать связь с условием индекса — и индекс останется невостребованным, хотя в каталоге он есть. Разберём типичные ошибки, их цену и способы проверить результат.

Как устроен частичный индекс

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

CREATE INDEX orders_open_created_idx
ON orders (created_at DESC)
WHERE status = 'open';

Он подходит для запросов, условие которых гарантирует, что выбранные строки входят в этот индекс. Но планировщик PostgreSQL не пытается доказать любую сложную логическую эквивалентность: надёжнее, когда предикат индекса явно повторён в запросе.

Термин. Предикат — условие, записанное после WHERE в определении индекса. В примере предикат — status = 'open'.

Частичный индекс — не просто «индекс поменьше». Это обещание планировщику: все строки, нужные этому запросу, находятся в индексируемом подмножестве.

Ошибки, из-за которых индекс не помогает

1. Условие запроса не доказывает предикат индекса

Индекс создан по status = 'open', а приложение отправляет запрос с условием status IN ('open', 'pending'). В таком результате могут быть строки pending, которых в индексе нет. Использовать этот индекс для полного ответа нельзя.

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

SELECT id, created_at
FROM orders
WHERE status = 'open'
  AND created_at >= TIMESTAMPTZ '2025-01-01 00:00:00+00';

Сначала сравните условия в запросе и определении индекса буквально. Затем проверьте план командой EXPLAIN: наличие индекса в базе само по себе ничего не говорит об использовании.

2. Параметр в запросе мешает доказать условие

Если приложение отправляет WHERE status = $1, планировщик не всегда может заранее заключить, что параметр будет равен 'open'. Это особенно важно для обобщённого плана подготовленного запроса: он рассчитан на разные значения параметра, в том числе на те, которым частичный индекс не подходит.

Это не означает, что запрос с параметром никогда не использует индекс. PostgreSQL может строить план с учётом конкретных значений, а решение зависит от типа плана и оценки стоимости. Проверяйте запрос именно в том виде, в каком его выполняет приложение. Если выборка всегда ограничена статусом open, оставьте это условие константой в запросе, а переменными передавайте остальные значения.

3. В предикате использована функция, результат которой меняется со временем

PostgreSQL требует, чтобы выражения в условии частичного индекса были неизменяемыми: для одинаковых аргументов они должны всегда давать одинаковый результат. Поэтому индекс с условием вроде created_at > now() - interval '30 days' создать нельзя: с течением времени граница «последних 30 дней» сдвигается, а записи в индексе сами собой не обновляются.

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

4. Индекс сделали уникальным, но ждут от него глобальной уникальности

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

CREATE UNIQUE INDEX users_active_email_uq
ON users (email)
WHERE deleted_at IS NULL;

Это подходит, если бизнес-правило звучит как «email уникален среди неудалённых пользователей». Если правило касается всех строк, частичный индекс не заменяет обычное ограничение уникальности. Кроме того, стандартное поведение уникальных индексов допускает несколько значений NULL; учитывайте это, если поле не обязательное.

5. Индекс выбрали по размеру, не проверив частоту и цену записи

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

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

Три варианта индексации — и чем за них платят

Сравнивать нужно не только размер. Важны форма запросов, доля подходящих строк и стоимость изменений таблицы.

Например, индекс (created_at DESC) WHERE status = 'open' соответствует выборке открытых заказов по времени. Если запрос ещё фильтрует по customer_id, проверьте, даст ли пользу ключ вида (customer_id, created_at DESC). Универсального порядка колонок нет: он зависит от условий, сортировки и планов запросов.

Как проверить индекс на реальном запросе

1. Возьмите запрос из приложения. Не переписывайте его ради красивого примера: сохраните параметры, фильтры и сортировку. Убедитесь, что условие запроса гарантирует предикат индекса.
2. Посмотрите план. Выполните EXPLAIN для запроса. Если проверяете время и фактическое число строк, используйте EXPLAIN (ANALYZE, BUFFERS) на безопасной нагрузке: ANALYZE действительно выполняет запрос.
3. Сверьте оценки с фактом. В плане сравните оценочное и фактическое число строк, узел доступа к таблице и чтение буферов. Последовательное сканирование не всегда ошибка: для небольшой таблицы или запроса, который возвращает большую её часть, оно может быть дешевле.
4. Проверьте несколько значений. Для параметризованного запроса протестируйте типичные значения и тот способ выполнения, который использует приложение. Один удачный план с литералом не гарантирует такой же план для подготовленного запроса.
5. Сравните цену до и после. Зафиксируйте время и буферы запроса, размер индекса и задержку записи в сопоставимых условиях. Без одинаковых данных и нагрузки сравнение легко вводит в заблуждение.

Когда выбирать частичный индекс

• Выбирайте частичный, если запросы стабильно ограничены небольшим подмножеством строк и это ограничение можно явно выразить простым предикатом.
• Выбирайте полный индекс, если приложение ищет по разным значениям, а доля подходящих строк велика или заранее неизвестна.
• Проверяйте составной вариант, если узкое место — не только отбор по статусу, но и сортировка или дополнительный фильтр.

Не принимайте решение по одному размеру индекса. Смотрите, какие запросы он обслуживает и как его поддержка влияет на запись. Индексация не заменяет корректную статистику и проверку плана.

Что проверить дальше

Начните с одного самого дорогого запроса и его точного предиката. Сопоставьте его с определением индекса, проверьте план на репрезентативных параметрах, затем измерьте время, буферы и влияние на запись. Если запросы используют несколько разных статусов или доля активных строк постоянно меняется, сравните частичный индекс с полным на той же выборке. Инвалидация кэша и настройка статистики — отдельные темы; здесь важно не приписывать индексу эффект, которого замеры не подтвердили.