Статья 2 июля 2026 г.

Частичный индекс в PostgreSQL: когда он лучше обычного

Практический разбор partial index в PostgreSQL: когда частичный индекс ускоряет запросы, чем он отличается от обычного индекса, как его проверить через EXPLAIN ANALYZE и каких ошибок избегать.

Обычный индекс в PostgreSQL строится по всем строкам таблицы. Это удобно, но не всегда эффективно.

Иногда приложение почти всегда ищет только небольшую часть данных:

активные заказы;
неоплаченные счета;
незавершенные задачи;
неудаленные записи при soft delete;
ошибки со статусом new;
опубликованные материалы;
одну актуальную версию записи из большого исторического справочника.

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

Для таких ситуаций в PostgreSQL есть частичный индекс — partial index.

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

Короткий пример

Допустим, есть таблица заказов:

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    status text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    paid_at timestamptz,
    deleted_at timestamptz
);

В таблице 20 миллионов строк, но активных заказов немного. Большая часть данных — закрытые, отмененные или архивные заказы.

Типичный запрос в приложении:

SELECT id, customer_id, status, created_at
FROM orders
WHERE customer_id = 10042
  AND deleted_at IS NULL
  AND status IN ('new', 'processing', 'paid')
ORDER BY created_at DESC
LIMIT 50;

Можно создать обычный индекс:

CREATE INDEX idx_orders_customer_created_at
ON orders (customer_id, created_at DESC);

Но такой индекс будет включать все строки: активные, закрытые, удаленные, архивные.

А можно создать частичный индекс только по тем строкам, которые действительно нужны этому запросу:

CREATE INDEX idx_orders_active_customer_created_at
ON orders (customer_id, created_at DESC)
WHERE deleted_at IS NULL
  AND status IN ('new', 'processing', 'paid');

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

Чем частичный индекс отличается от обычного

Обычный индекс:

CREATE INDEX idx_orders_status
ON orders (status);

Индексирует все строки таблицы.

Частичный индекс:

CREATE INDEX idx_orders_unpaid
ON orders (created_at)
WHERE paid_at IS NULL;

Индексирует только строки, где paid_at IS NULL.

Это важно не только для чтения, но и для записи. Чем меньше индекс, тем меньше работы PostgreSQL должен выполнять при INSERT, UPDATE и DELETE. Официальная документация прямо отмечает, что исключение часто встречающихся или неинтересных значений уменьшает размер индекса и может ускорять операции обновления таблицы, потому что индекс не нужно менять во всех случаях.

Когда частичный индекс лучше обычного

1. Когда запросы работают только с небольшой частью таблицы

Это самый частый и самый полезный сценарий.

Например, есть таблица задач:

CREATE TABLE tasks (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    project_id bigint NOT NULL,
    assignee_id bigint,
    status text NOT NULL,
    priority int NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now(),
    closed_at timestamptz
);

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

SELECT id, project_id, assignee_id, status, priority, created_at
FROM tasks
WHERE project_id = 17
  AND closed_at IS NULL
ORDER BY priority DESC, created_at DESC
LIMIT 100;

Хороший частичный индекс:

CREATE INDEX idx_tasks_open_project_priority
ON tasks (project_id, priority DESC, created_at DESC)
WHERE closed_at IS NULL;

Такой индекс не содержит закрытые задачи. Если закрытых задач в десятки раз больше, чем открытых, индекс будет заметно меньше обычного.

2. Когда нужно ускорить редкий, но важный статус

Представим таблицу фоновых заданий:

CREATE TABLE jobs (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    queue_name text NOT NULL,
    status text NOT NULL,
    run_at timestamptz NOT NULL,
    payload jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

В таблице много заданий со статусами done и failed, но воркер постоянно ищет только задания pending:

SELECT id, payload
FROM jobs
WHERE queue_name = 'email'
  AND status = 'pending'
  AND run_at <= now()
ORDER BY run_at
LIMIT 10;

Индекс:

CREATE INDEX idx_jobs_pending_queue_run_at
ON jobs (queue_name, run_at)
WHERE status = 'pending';

Здесь частичный индекс лучше обычного индекса по (queue_name, status, run_at), если pending — маленькая доля таблицы.

3. Когда используется soft delete

Во многих приложениях строки не удаляются физически, а помечаются как удаленные:

deleted_at timestamptz

Тогда почти все пользовательские запросы содержат условие:

WHERE deleted_at IS NULL

Например:

SELECT id, email, full_name
FROM users
WHERE lower(email) = lower('user@example.com')
  AND deleted_at IS NULL;

Можно создать частичный индекс:

CREATE INDEX idx_users_active_email_lower
ON users (lower(email))
WHERE deleted_at IS NULL;

Если в таблице хранится много удаленных или архивных пользователей, обычный индекс по lower(email) будет больше, чем нужно.

4. Когда нужна уникальность только для части строк

Частичный индекс может быть не только обычным, но и уникальным.

Например, нужно разрешить повторное использование email у удаленных пользователей, но среди активных пользователей email должен быть уникальным:

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

Теперь две удаленные записи могут иметь одинаковый email, но две активные — нет.

Это очень полезный прием для:

soft delete;
версионирования записей;
хранения истории;
бизнес-правил вида «активная запись должна быть только одна».

В документации PostgreSQL отдельно описан сценарий, когда частичный уникальный индекс используется для ограничения уникальности только среди строк, удовлетворяющих условию.

5. Когда нужно исключить слишком частые значения

Иногда значение настолько часто встречается, что индекс по нему бесполезен.

Например, в таблице событий 95% строк имеют статус ok, а расследуются в основном ошибки:

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    service_name text NOT NULL,
    level text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    message text NOT NULL
);

Частый запрос:

SELECT id, created_at, message
FROM events
WHERE service_name = 'billing'
  AND level IN ('error', 'critical')
ORDER BY created_at DESC
LIMIT 100;

Индекс:

CREATE INDEX idx_events_errors_service_created_at
ON events (service_name, created_at DESC)
WHERE level IN ('error', 'critical');

Такой индекс не хранит строки с level = 'info' или level = 'ok'.

Как PostgreSQL понимает, что можно использовать частичный индекс

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

Например, есть индекс:

CREATE INDEX idx_orders_active
ON orders (customer_id, created_at DESC)
WHERE deleted_at IS NULL;

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

SELECT *
FROM orders
WHERE customer_id = 10042
  AND deleted_at IS NULL
ORDER BY created_at DESC;

А этот — нет:

SELECT *
FROM orders
WHERE customer_id = 10042
ORDER BY created_at DESC;

Даже если приложение «логически» показывает только активные заказы, PostgreSQL не может догадаться об этом, если условия нет в SQL-запросе.

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

Частая ошибка: индекс создан, но PostgreSQL его не использует

Допустим, создан индекс:

CREATE INDEX idx_tasks_open_project
ON tasks (project_id, created_at DESC)
WHERE closed_at IS NULL;

Разработчик ожидает, что он будет использоваться в запросе:

SELECT *
FROM tasks
WHERE project_id = 17
  AND status <> 'closed'
ORDER BY created_at DESC;

Но PostgreSQL может не использовать индекс, потому что в запросе нет условия:

closed_at IS NULL

Для человека status <> 'closed' и closed_at IS NULL могут означать почти одно и то же. Для планировщика PostgreSQL это разные условия.

Лучше написать запрос так:

SELECT *
FROM tasks
WHERE project_id = 17
  AND closed_at IS NULL
ORDER BY created_at DESC;

Или пересмотреть сам индекс, если реальные запросы фильтруют данные именно по status.

Проверяем через EXPLAIN ANALYZE

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

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, project_id, assignee_id, status, priority, created_at
FROM tasks
WHERE project_id = 17
  AND closed_at IS NULL
ORDER BY priority DESC, created_at DESC
LIMIT 100;

В хорошем случае в плане будет что-то похожее на:

Index Scan using idx_tasks_open_project_priority on tasks

или:

Bitmap Index Scan on idx_tasks_open_project_priority

Если вместо этого PostgreSQL делает Seq Scan, нужно проверить:

совпадает ли условие запроса с WHERE-условием индекса;
достаточно ли мала доля строк, попадающих в частичный индекс;
актуальна ли статистика после ANALYZE;
не слишком ли маленькая таблица;
действительно ли запрос возвращает небольшую часть данных;
не мешают ли параметры подготовленных запросов.

Параметризованные запросы и частичные индексы

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

Например, индекс:

CREATE INDEX idx_events_errors
ON events (service_name, created_at DESC)
WHERE level = 'error';

Запрос с явно указанным значением:

SELECT *
FROM events
WHERE service_name = 'billing'
  AND level = 'error'
ORDER BY created_at DESC
LIMIT 100;

может использовать индекс.

А подготовленный запрос вида:

SELECT *
FROM events
WHERE service_name = $1
  AND level = $2
ORDER BY created_at DESC
LIMIT 100;

не всегда сможет использовать именно этот частичный индекс, потому что на этапе планирования PostgreSQL не всегда может доказать, что $2 всегда будет равно 'error'.

В документации PostgreSQL прямо указано, что сопоставление условия запроса с предикатом частичного индекса происходит во время планирования, а не во время выполнения; поэтому параметризованные условия могут не работать с частичным индексом так, как ожидается.

Практический вывод: если вы делаете частичный индекс под конкретный статус, проверьте реальный SQL, который отправляет приложение, а не только пример запроса из головы.

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

Список частичных индексов можно получить через pg_indexes:

SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE indexdef ILIKE '% WHERE %'
ORDER BY schemaname, tablename, indexname;

Более точный вариант — через системные каталоги PostgreSQL:

SELECT
    n.nspname AS schema_name,
    t.relname AS table_name,
    i.relname AS index_name,
    pg_get_indexdef(i.oid) AS index_definition,
    pg_get_expr(ix.indpred, ix.indrelid) AS predicate
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE ix.indpred IS NOT NULL
ORDER BY n.nspname, t.relname, i.relname;

У частичного индекса в pg_index заполнено поле indpred, где хранится выражение предиката.

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

Можно посмотреть размер индексов таблицы:

SELECT
    i.relname AS index_name,
    pg_size_pretty(pg_relation_size(i.oid)) AS index_size,
    pg_relation_size(i.oid) AS index_size_bytes
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE n.nspname = 'public'
  AND t.relname = 'orders'
ORDER BY pg_relation_size(i.oid) DESC;

Если частичный индекс занимает почти столько же места, сколько обычный, стоит задать вопрос: действительно ли он нужен?

Частичный индекс особенно полезен, когда условие WHERE отбирает небольшую и часто используемую часть таблицы.

Как понять, используется ли индекс

Для этого удобно смотреть статистику по индексам:

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

Поле idx_scan показывает, сколько раз индекс использовался для сканирования.

Но важно не делать поспешных выводов. Если индекс создан недавно, статистики может быть мало. Если запрос редкий, но критически важный, у индекса может быть маленький idx_scan, но он все равно нужен.

Частичный индекс и INCLUDE

Иногда можно совместить частичный индекс и INCLUDE, чтобы PostgreSQL мог выполнить запрос как Index Only Scan.

Например:

CREATE INDEX idx_orders_active_customer_covering
ON orders (customer_id, created_at DESC)
INCLUDE (status, paid_at)
WHERE deleted_at IS NULL;

Такой индекс:

фильтрует только активные строки;
сортируется по customer_id и created_at;
дополнительно хранит status и paid_at как включенные колонки.

INCLUDE может помочь для Index Only Scan, когда запросу нужны колонки, уже находящиеся в индексе. Но добавлять включенные колонки нужно осторожно: они увеличивают размер индекса. Документация PostgreSQL отдельно предупреждает, что включенные колонки дублируют данные из таблицы и могут раздувать индекс.

Создание частичного индекса в production

На большой таблице обычный CREATE INDEX может быть проблемой, потому что он блокирует запись в таблицу на время построения индекса.

Для production чаще используют:

CREATE INDEX CONCURRENTLY idx_orders_active_customer_created_at
ON orders (customer_id, created_at DESC)
WHERE deleted_at IS NULL
  AND status IN ('new', 'processing', 'paid');

CREATE INDEX CONCURRENTLY строит индекс без блокировок, которые запрещают параллельные INSERT, UPDATE и DELETE, хотя у этого режима есть свои ограничения и накладные расходы.

Практические правила:

Не создавайте индекс на большой таблице в рабочее время без проверки.
Используйте CREATE INDEX CONCURRENTLY для production-таблиц.
Сначала проверьте запрос через EXPLAIN (ANALYZE, BUFFERS) на тестовой или staging-базе.
После создания индекса проверьте, изменился ли план выполнения.
Не забывайте, что лишние индексы замедляют запись.

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

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

1. Условие выбирает слишком много строк

Если условие попадает в 70–90% таблицы, пользы может почти не быть.

Например:

WHERE deleted_at IS NULL

может быть плохим предикатом, если удаленных строк всего 1%.

В этом случае частичный индекс почти такой же большой, как обычный, но менее универсальный.

2. Запросы не содержат условие индекса

Если индекс создан так:

WHERE status = 'pending'

а реальные запросы используют:

WHERE status IN ('pending', 'retry')

или:

WHERE status <> 'done'

PostgreSQL может не использовать индекс.

3. Вы пытаетесь заменить партиционирование множеством частичных индексов

Плохая идея:

CREATE INDEX idx_events_2024 ON events (created_at) WHERE event_year = 2024;
CREATE INDEX idx_events_2025 ON events (created_at) WHERE event_year = 2025;
CREATE INDEX idx_events_2026 ON events (created_at) WHERE event_year = 2026;

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

Документация PostgreSQL прямо предупреждает, что не стоит использовать набор непересекающихся частичных индексов как замену партиционированию: планировщик не понимает отношения между такими индексами так же хорошо, как между партициями.

4. Распределение данных постоянно меняется

Частичный индекс хорош, когда вы понимаете распределение данных.

Например, status = 'pending' — это всегда 1–2% таблицы.

Но если сегодня pending — 2%, а завтра из-за сбоя — 60%, индекс может стать менее полезным.

Хороший чек-лист перед созданием partial index

Перед тем как создать частичный индекс, ответьте на вопросы:

Какие реальные запросы должны ускориться?
Есть ли эти запросы в логах, pg_stat_statements или приложении?
Какое условие повторяется в этих запросах?
Какую долю таблицы отбирает это условие?
Будет ли PostgreSQL видеть это условие прямо в SQL?
Не является ли задача на самом деле задачей для составного индекса?
Не является ли задача задачей для партиционирования?
Не станет ли индекс лишней нагрузкой на INSERT, UPDATE и DELETE?
Проверен ли план через EXPLAIN (ANALYZE, BUFFERS)?
Безопасно ли создавать индекс в production?

Пример полного разбора

Допустим, есть таблица уведомлений:

CREATE TABLE notifications (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id bigint NOT NULL,
    status text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    read_at timestamptz,
    payload jsonb NOT NULL
);

Пользователь открывает список непрочитанных уведомлений:

SELECT id, created_at, payload
FROM notifications
WHERE user_id = 10042
  AND read_at IS NULL
ORDER BY created_at DESC
LIMIT 50;

В таблице 100 миллионов уведомлений. Непрочитанных — около 3%.

Обычный индекс:

CREATE INDEX idx_notifications_user_created_at
ON notifications (user_id, created_at DESC);

будет содержать все 100 миллионов строк.

Частичный индекс:

CREATE INDEX CONCURRENTLY idx_notifications_unread_user_created_at
ON notifications (user_id, created_at DESC)
WHERE read_at IS NULL;

будет содержать только непрочитанные уведомления.

Проверяем:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, payload
FROM notifications
WHERE user_id = 10042
  AND read_at IS NULL
ORDER BY created_at DESC
LIMIT 50;

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

Но если запрос в приложении написан так:

SELECT id, created_at, payload
FROM notifications
WHERE user_id = 10042
  AND status = 'unread'
ORDER BY created_at DESC
LIMIT 50;

частичный индекс по read_at IS NULL может не использоваться.

В таком случае нужно либо изменить запрос, либо создать индекс под реальное условие:

CREATE INDEX CONCURRENTLY idx_notifications_unread_status_user_created_at
ON notifications (user_id, created_at DESC)
WHERE status = 'unread';

Как pgtools помогает при работе с частичными индексами

Частичные индексы редко создаются «на глаз». Обычно нужно пройти несколько шагов:

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

В pgtools это удобно делать в одном рабочем окне:

открыть таблицу и посмотреть ее DDL;
быстро найти существующие индексы;
выполнить диагностические SQL-запросы;
сравнить планы EXPLAIN ANALYZE;
сохранить полезные запросы в отдельных вкладках;
аккуратно подготовить DDL для создания индекса;
использовать lock_timeout при выполнении DDL в production.

Частичный индекс — хороший пример задачи, где важно не просто написать CREATE INDEX, а понять реальную нагрузку, реальные запросы и поведение планировщика PostgreSQL.

Краткий вывод

Частичный индекс в PostgreSQL лучше обычного, когда:

запросы работают только с небольшой частью таблицы;
условие этой части стабильно и явно присутствует в SQL;
обычный индекс получается слишком большим;
нужно ускорить доступ к активным, незавершенным, неоплаченным или неудаленным строкам;
нужно обеспечить уникальность только для части данных;
вы проверили результат через EXPLAIN (ANALYZE, BUFFERS).

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

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