Привет, Хабр!

Сегодня рассмотрим про то, что такое Partial Index в PostgreSQL и как он может ускорить редкие выборки, сэкономить дисковое пространство и облегчить жизнь планировщику запросов.

Проблема: хочется быстро, но не платить памятью

Допустим, есть простая таблица users:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  status TEXT NOT NULL, -- 'active', 'inactive', 'deleted'
  deleted_at TIMESTAMP
);

2 миллиона записей. Из них активных — всего 50 тысяч. Остальные удалены (deleted_at IS NOT NULL).

Запрос на выборку:

SELECT * FROM users WHERE deleted_at IS NULL;

Идеально, если он быстрый. Но если мы просто создадим индекс вот так:

CREATE INDEX idx_users_deleted_at ON users(deleted_at);

…то всё становится плохо. Индекс содержит все строки. И когда делаемWHERE deleted_at IS NULL, PostgreSQL может... проигнорировать индекс, потому что он не даёт селективности.

В итоге — полный просмотр таблицы или полный просмотр индекса, который тоже тормозит. Мы хотели ускорить, а стало хуже. Почему?

Partial Index: фокус на нужном

Решение:

CREATE INDEX idx_users_active_only ON users(id)
WHERE deleted_at IS NULL;

WHERE в CREATE INDEX — partial index, или частичный индекс.

В этот индекс попадут только строки, где deleted_at IS NULL. То есть — только активные пользователи. 50k записей вместо 2 миллионов. Индекс весит в 40 раз меньше, и теперь Postgres может использовать его быстро.

Но что действительно важно — как он это делает.

Как работает partial index

Частичный индекс — это не просто “индекс с фильтром”. Это двойной контракт между нами и планировщиком PostgreSQL.

Что происходит при создании partial index:

  1. Физически в индекс попадают только те строки, которые удовлетворяют WHERE-условию. В нашем случае — только где deleted_at IS NULL.

  2. PostgreSQL хранит условие как часть метаданных индекса.

  3. Когда делаем запрос, в котором WHERE deleted_at IS NULL, оптимизатор сопоставляет это условие с условием из индекса. И если он понимает: «ага, запрос точно попадает в фильтр индекса» — то он может использовать этот индекс. Если не попадает — игнорирует.

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

  • WHERE deleted_at IS NULL работает.

  • WHERE deleted_at IS NULL AND status = 'active' — тоже сработает, если Postgres сможет вывести, что условие включает в себя deleted_at IS NULL.

  • А вот WHERE COALESCE(deleted_at, now()) = now() — уже нет, потому что планировщик не может доказать, что это то же самое.

Как планиров��ик это решает?

Когда мы пишем запрос, Postgres не просто смотрит на текст запроса. Он строит дерево выражений, и сравнивает его с деревом условий в partial index. Если дерево из запроса логически включает дерево из индекса — можно использовать.

Это важно. Например:

CREATE INDEX idx_active ON users(id) WHERE deleted_at IS NULL;

И вот такой запрос:

SELECT * FROM users WHERE deleted_at IS NULL;

Здесь всё чётко — деревья совпадают → индекс применяется.

Но если напишем:

SELECT * FROM users WHERE deleted_at IS NOT DISTINCT FROM NULL;

— всё, поезд ушёл. Хотя это логически то же самое, planner не умеет так глубоко оптимизировать. Поэтому пиши так, как писал в индекс.

И как теперь работает запрос?

С тем partial index'ом:

CREATE INDEX idx_users_active_only ON users(id)
WHERE deleted_at IS NULL;

Запрос:

EXPLAIN ANALYZE
SELECT * FROM users WHERE deleted_at IS NULL;

даст:

Index Scan using idx_users_active_only on users ...

Planner использует только 50k строк, а не 2 миллиона. Вся суть в том, что фильтр совпал. И Postgres гарантирует, что только нужные строки в индексе — значит, он может использовать его без проверок.

Partial index vs обычный индекс + фильтр в запросе

Многие думают: ну я просто добавлю фильтр в WHERE, и Postgres сам поймёт. Увы, не поймёт.

Вот так:

CREATE INDEX idx_users_id ON users(id);

SELECT * FROM users WHERE id = 123 AND deleted_at IS NULL;

Postgres не может использовать idx_users_id эффективно, если deleted_at IS NULL — селективный и часто обнуляет индекс.

А вот с partial index:

CREATE INDEX idx_active_users ON users(id) WHERE deleted_at IS NULL;

SELECT * FROM users WHERE id = 123 AND deleted_at IS NULL;

— попадание в индекс 100%. Потому что WHERE запроса покрывает фильтр индекса.

Возможные проблемы

Поля с NULL

Partial index с IS NULL — это прекрасно. Но помни, что NULL — коварная штука. Например:

WHERE status = 'active'

Или:

WHERE is_blocked = FALSE

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

Выражения и нестабильные функции

CREATE INDEX idx_valid_tokens ON tokens(token)
WHERE expires_at > now();

Ошибка новичка. now() — нестабильная функция. Сегодня одно, завтра другое. Postgres не может гарантировать, что индекс будет актуален, и использовать его почти никогда не будет.

Решение: добавляем is_valid BOOLEAN GENERATED ALWAYS AS (...) STORED, и фильтруем по нему.

Странные выражения

WHERE lower(email) LIKE '%gmail.com'

Звучит круто, но Postgres не поймёт. Он не может преобразовать email в lower(email) на ходу. Всё должно быть в лоб. Или добавляем generated column.

Как использовать partial index с мозгами

Подходит, если:

  • Запрос всегда содержит одни и те же условия deleted_at IS NULL

  • Выборка редкая (меньше 10%)

  • Устали от жирных индексов и медленных планов

Не подходит:

  • Условие непостоянное now(), random() и т.п.

  • Запросы слишком разные, не повторяют условие

  • Надо часто обновлять фильтруемые поля (индексы будут постоянно перестраиваться)

Работаете с PostgreSQL глубже, чем просто SQL-запросы? В Otus запланированы открытые уроки — про то, что сегодня важно знать администратору, и как обеспечить отказоустойчивость в облаке. Всё на реальных кейсах, без абстракций.

Темы ближайших встреч:

  • 15 апреля — Карьера PostgreSQL-администратора в реалиях 2025: что хотят работодатели и на чем нужно делать акцент соискателям. Записаться

  • 24 апреля — Резервное копирование и репликация PostgreSQL в облаке: обеспечение высокой доступности и сохранности данных. Записаться