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

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

В реальной базе запросы редко ходят по данным равномерно. Обычно приложение снова и снова ищет одни и те же «горячие» строки:

• новые заказы со статусом pending;
• активных пользователей;
• незакрытые задачи;
• товары, которые сейчас опубликованы;
• записи, у которых deleted_at IS NULL.

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

Для таких случаев и нужен частичный индекс.

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

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

Частичный индекс — это обычный индекс с условием WHERE.

В PostgreSQL он создаётся так:

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Такой индекс говорит базе:

Индексируй не все строки из orders, а только те, где status = 'pending'.

Сравним с обычным индексом:

CREATE INDEX idx_orders_status
  ON orders (status);

Этот индекс попадёт на каждую строку таблицы: и на pending, и на paid, и на cancelled, и на любые другие статусы.

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

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

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

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

Как запрос использует частичный индекс

Допустим, приложение часто показывает оператору новые ожидающие заказы:

SELECT id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at;

Для такого запроса частичный индекс подходит идеально:

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

База видит: запросу нужны только строки status = 'pending', а индекс как раз содержит только такие строки, уже отсортированные по created_at.

Это важный момент: условие запроса должно совпадать с условием индекса или быть более узким.

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

SELECT id, created_at
FROM orders
WHERE status = 'pending'
  AND created_at >= DATE '2026-01-01'
ORDER BY created_at;

Почему? Потому что он всё равно ищет только pending, просто добавляет ещё одно ограничение по дате.

А вот такой запрос уже не подходит:

SELECT id, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at;

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

Почему частичный индекс быстрее

Главная идея простая: чем меньше индекс, тем дешевле с ним работать.

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

С индексами похожая история.

Меньше размер на диске

Полный индекс хранит записи для всей таблицы. Частичный — только для нужной части.

Если «горячих» строк мало, разница может быть огромной.

Например:

• всего заказов: 50 000 000;
• заказов pending: 7 000.

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

Больше шансов попасть в память

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

А чтение из памяти намного быстрее, чем чтение с диска.

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

Быстрее запись

Каждый индекс — это не только ускорение чтения, но и дополнительная работа при записи.

Когда вы вставляете строку в таблицу, база должна обновить подходящие индексы. Чем больше индексов, тем дороже INSERT, UPDATE и DELETE.

Но у частичного индекса есть приятное свойство: если строка не подходит под условие, индекс вообще не трогается.

Например:

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Если вы вставляете заказ со статусом paid, он не попадёт в этот индекс. Базе не нужно добавлять туда новую запись.

Дешевле обслуживание

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

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

Пример: очередь задач

Классический пример для частичного индекса — очередь задач.

Допустим, есть таблица jobs. В неё пишутся задачи для фоновых воркеров:

CREATE TABLE jobs (
  id bigint,
  state text,
  priority integer,
  created_at timestamp,
  payload jsonb
);

У задачи могут быть разные состояния:

• queued — ждёт выполнения;
• running — выполняется;
• done — завершена;
• failed — завершилась с ошибкой.

Воркеры почти всегда ищут только задачи, которые ещё надо взять в работу:

SELECT id, payload
FROM jobs
WHERE state IN ('queued', 'running')
ORDER BY priority DESC, created_at
LIMIT 1;

Если сделать полный индекс по состоянию, приоритету и дате, туда попадут и миллионы старых задач done.

А они воркеру почти никогда не нужны.

Лучше сделать частичный индекс:

CREATE INDEX idx_jobs_queue
  ON jobs (priority DESC, created_at)
  WHERE state IN ('queued', 'running');

Теперь индекс содержит только живую очередь.

Даже если в таблице лежат сотни миллионов выполненных задач, индекс для воркеров остаётся маленьким и быстрым.

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

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

Плохая идея:

CREATE INDEX idx_recent_orders
  ON orders (created_at)
  WHERE created_at > now() - interval '7 days';

На первый взгляд звучит удобно: индексировать только заказы за последние 7 дней.

Но есть проблема: now() меняется. Сегодня «последние 7 дней» — один диапазон, завтра — уже другой. Индекс физически не будет сам каждую секунду переосмысливать, какие старые строки надо убрать, а какие новые добавить по изменившемуся времени.

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

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Или:

CREATE INDEX idx_users_active
  ON users (created_at)
  WHERE deleted_at IS NULL;

Хорошие условия для частичных индексов обычно выглядят так:

• status = 'pending';
• state IN ('queued', 'running');
• deleted_at IS NULL;
• is_active = true;
• published_at IS NOT NULL.

То есть это не «скользящее время», а понятный флаг, статус или состояние строки.

Частичный индекс для активных пользователей

Допустим, в таблице users хранятся все пользователи: активные, заблокированные и удалённые.

CREATE TABLE users (
  id bigint,
  email text,
  name text,
  is_active boolean,
  deleted_at timestamp,
  created_at timestamp
);

Приложение часто ищет активного пользователя по email:

SELECT id, name
FROM users
WHERE email = 'alex@example.com'
  AND deleted_at IS NULL;

Можно сделать полный индекс:

CREATE INDEX idx_users_email
  ON users (email);

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

Лучше сделать частичный индекс только по живым пользователям:

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

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

SELECT id, name
FROM users
WHERE email = 'alex@example.com'
  AND deleted_at IS NULL;

А старые удалённые строки не будут раздувать индекс.

Частичный UNIQUE: уникальность только среди нужных строк

Самое мощное применение частичного индекса — частичный уникальный индекс.

Он позволяет сказать:

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

Это особенно полезно при soft delete.

Проблема soft-delete

soft delete — это когда строку не удаляют физически, а помечают удалённой.

Например:

UPDATE users
SET deleted_at = now()
WHERE id = 10;

Строка остаётся в таблице, но приложение считает её удалённой.

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

Обычный уникальный индекс выглядит так:

CREATE UNIQUE INDEX idx_users_email
  ON users (email);

Но он запрещает одинаковый email во всей таблице, включая удалённые строки.

Получается неприятная ситуация:

1. Пользователь зарегистрировался с email alex@example.com.
2. Потом удалил аккаунт.
3. Через месяц захотел зарегистрироваться снова.
4. База не даёт, потому что старая удалённая строка всё ещё хранит этот email.

Для бизнеса это странно: аккаунт удалён, почему email всё ещё заблокирован?

Решение: UNIQUE только для живых строк

В PostgreSQL можно сделать так:

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

Теперь правило звучит точнее:

Среди активных пользователей email должен быть уникальным.

Удалённых строк с одинаковым email может быть сколько угодно. Но две активные строки с одним email база не пропустит.

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

Пример: один основной адрес на пользователя

Ещё один хороший пример — адреса пользователя.

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

Таблица:

CREATE TABLE addresses (
  id bigint,
  user_id bigint,
  city text,
  street text,
  is_primary boolean
);

Обычный UNIQUE (user_id) не подойдёт, потому что тогда у пользователя вообще сможет быть только один адрес.

А нам нужно другое правило:

У пользователя может быть много адресов, но только один адрес с is_primary = true.

Это удобно выразить частичным уникальным индексом:

CREATE UNIQUE INDEX idx_one_primary_address
  ON addresses (user_id)
  WHERE is_primary = true;

Теперь база разрешит такие строки:

user_id	street	is_primary
10	Main Street	true
10	Oak Street	false
10	Pine Street	false

Но не разрешит добавить второй основной адрес для того же пользователя:

user_id	street	is_primary
10	Main Street	true
10	River Street	true

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

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

Частичный индекс полезен не всегда.

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

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

Допустим, в таблице 10 миллионов пользователей, и 9 миллионов из них активны.

Индекс:

CREATE INDEX idx_users_active
  ON users (created_at)
  WHERE is_active = true;

будет почти таким же большим, как полный индекс. Да, он исключит 10% строк, но это может не дать заметного выигрыша.

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

• 1%;
• 5%;
• 10%;
• иногда 20%, если запрос очень частый и важный.

Но если предикат покрывает почти всю таблицу, «частичность» теряет смысл.

Запрос не повторяет условие индекса

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

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

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

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

SELECT id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at;

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

SELECT id, created_at
FROM orders
ORDER BY created_at;

Почему? Потому что индекс содержит не все заказы, а только pending. Для запроса по всей таблице он неполный.

Запросы бывают слишком разными

Если сегодня вы ищете pending, завтра paid, послезавтра cancelled, а потом вообще все статусы сразу, один частичный индекс не спасёт.

Частичный индекс лучше всего работает для повторяющегося сценария:

WHERE status = 'pending'

или:

WHERE deleted_at IS NULL

или:

WHERE state IN ('queued', 'running')

То есть для условия, которое встречается постоянно и важно для скорости.

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

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

В PostgreSQL для этого используют EXPLAIN.

EXPLAIN
SELECT id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at;

А если нужно увидеть реальное выполнение, используют EXPLAIN (ANALYZE):

EXPLAIN (ANALYZE)
SELECT id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at;

В плане стоит искать упоминание вашего индекса, например idx_orders_pending.

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

• условие запроса не совпадает с условием индекса;
• таблица маленькая, и базе дешевле прочитать её целиком;
• статистика устарела;
• индекс выбирает слишком большую часть таблицы;
• сортировка или фильтрация не совпадает с порядком колонок в индексе.

Иногда после создания индекса полезно обновить статистику:

ANALYZE orders;

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

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

Например:

SELECT pg_size_pretty(pg_relation_size('idx_orders_pending')) AS index_size;

Можно сравнить полный и частичный индекс:

SELECT
  pg_size_pretty(pg_relation_size('idx_orders_status'))  AS full_index_size,
  pg_size_pretty(pg_relation_size('idx_orders_pending')) AS partial_index_size;

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

Частичный индекс и порядок колонок

Частичный индекс — это всё равно обычный индекс. Поэтому порядок колонок в нём важен.

Например, есть запрос:

SELECT id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at;

Для него логично создать индекс по created_at, а статус вынести в условие индекса:

CREATE INDEX idx_orders_pending_created_at
  ON orders (created_at)
  WHERE status = 'pending';

Но если запрос внутри pending ещё часто фильтрует по менеджеру:

SELECT id, created_at
FROM orders
WHERE status = 'pending'
  AND manager_id = 42
ORDER BY created_at;

может быть полезнее такой индекс:

CREATE INDEX idx_orders_pending_manager_created
  ON orders (manager_id, created_at)
  WHERE status = 'pending';

Здесь индекс содержит только заказы pending, а внутри этого подмножества помогает быстро найти строки конкретного менеджера и отдать их в порядке created_at.

То есть думать нужно не только о предикате, но и о самом запросе:

• по каким колонкам фильтруем;
• по каким сортируем;
• какие строки выбираем чаще всего.

Различия между СУБД

Частичные индексы поддерживаются не везде одинаково.

PostgreSQL

В PostgreSQL частичные индексы поддерживаются полноценно:

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Также работают частичные уникальные индексы:

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

Для задач с soft delete, очередями и активными сущностями это один из самых удобных инструментов.

SQLite

SQLite тоже поддерживает частичные индексы с синтаксисом CREATE INDEX ... WHERE.

Например:

CREATE INDEX idx_tasks_open
  ON tasks (created_at)
  WHERE closed_at IS NULL;

Частичный UNIQUE тоже возможен:

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

MySQL

В MySQL/InnoDB настоящих частичных индексов в стиле PostgreSQL нет.

То есть такой синтаксис не сработает:

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Часто используют обходные пути:

• сгенерированный столбец;
• индекс по выражению;
• отдельную таблицу для «горячих» строк;
• архитектурное разделение активных и архивных данных.

Важно не путать частичный индекс с префиксным индексом MySQL.

Вот это не частичный индекс:

CREATE INDEX idx_users_email_prefix
  ON users (email(10));

Такой индекс берёт только первые 10 символов значения email. Он ограничивает длину индексируемого значения, а не количество строк в индексе.

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

Какие строки индексировать?

Префиксный индекс отвечает на вопрос:

Какую часть значения индексировать?

Это разные вещи.

SQL Server

В SQL Server похожий механизм называется filtered index.

Пример:

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Идея такая же: индекс содержит только строки, которые проходят фильтр.

ClickHouse

ClickHouse устроен иначе: это колоночная аналитическая СУБД, и там обычно думают не в терминах обычных B-tree индексов.

Вместо частичных индексов чаще используют:

• PARTITION BY;
• сортировочный ключ;
• data-skipping индексы;
• правильную модель хранения.

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

Практический рецепт

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

Первый: есть ли в таблице маленькое подмножество строк, которое читается намного чаще остальных?

Например:

WHERE status = 'pending'

или:

WHERE deleted_at IS NULL

Второй: это условие стабильно встречается в запросах?

Если приложение постоянно ищет активные строки, это хороший знак.

Третий: это подмножество сильно меньше всей таблицы?

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

Четвёртый: можно ли выразить правило простым условием?

Хорошо:

WHERE status = 'pending'

Хорошо:

WHERE deleted_at IS NULL

Плохо:

WHERE created_at > now() - interval '7 days'

Пятый: подтвердил ли EXPLAIN (ANALYZE), что индекс реально используется?

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

Пример полного пути

Допустим, у нас есть медленный запрос:

SELECT id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;

Таблица большая, старых заказов много, а pending мало.

Создаём частичный индекс:

CREATE INDEX idx_orders_pending_created_at
  ON orders (created_at)
  WHERE status = 'pending';

Обновляем статистику:

ANALYZE orders;

Проверяем план:

EXPLAIN (ANALYZE)
SELECT id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;

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

Что важно запомнить

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

Он особенно полезен для «горячих» подмножеств данных:

• ожидающие заказы;
• активные пользователи;
• незакрытые задачи;
• неудалённые строки;
• текущая очередь фоновых задач.

В PostgreSQL частичный индекс создаётся так:

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Частичный UNIQUE позволяет задавать аккуратные правила целостности:

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

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

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

Лучший подход простой: найдите частый запрос с узким условием, создайте индекс с таким же WHERE, проверьте результат через EXPLAIN (ANALYZE) и сравните размер индекса. Часто это даёт сильное ускорение без сложной переделки базы.