Привет, Хабр!
Сегодня рассмотрим про то, что такое 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:
Физически в индекс попадают только те строки, которые удовлетворяют
WHERE-условию. В нашем случае — только гдеdeleted_at IS NULL.PostgreSQL хранит условие как часть метаданных индекса.
Когда делаем запрос, в котором
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 в облаке: обеспечение высокой доступности и сохранности данных. Записаться