SQL Academy · Урок

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

Индексируйте только нужные строки с помощью предложений WHERE и индексируйте вычисляемые выражения (lower(email), date_trunc('day', ts))

«Частичные индексы и индексы по выражениям» — бесплатный урок SQL Academy на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

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

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

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

Когда частичные индексы особенно эффективны

Используйте их, если:

Вы всегда фильтруете по одному и тому же предикату (например, при мягком удалении)
Одно значение преобладает в столбце (например, 95% строк имеют status='закрыто')
Вам нужен небольшой часто используемый индекс, помещающийся в кэш

Пример: активные сеансы

Большинство сеансов уже истекло, и запросы выполняются только для активных:

CREATE INDEX sessions_active_idx ON sessions(user_id)
  WHERE expires_at > NOW();
-- Caveat: planner can't use NOW() in the index predicate — use a fixed timestamp
-- and reindex periodically, OR use a boolean column.

Лучше: используйте стабильный предикат

Предикат индекса должен быть неизменяемым. Выражения, зависящие от времени (NOW()), не подходят. Вместо них используйте столбец, например is_active:

CREATE INDEX sessions_active_idx ON sessions(user_id)
  WHERE is_active = true;

-- The query must use the same predicate to match the index:
SELECT * FROM sessions WHERE is_active = true AND user_id = 42;

Индекс по выражению

Индексируйте результат функции от столбца:

CREATE INDEX users_email_lower_idx ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@Example.com');
-- Uses the expression index.

Распространённые индексы по выражениям

LOWER(email) для регистронезависимого поиска
(price * tax_rate) для сортировки по вычисляемому значению
date_trunc('day', created_at) для ежедневных отчётов
(data ->> 'user_id')::BIGINT для извлечения данных из JSONB

Выражение должно быть IMMUTABLE

Выражение должно быть помечено как IMMUTABLE — его результат зависит только от входных данных. RANDOM(), NOW(), CURRENT_USER не подходят.

Комбинация частичного индекса и индекса по выражению

Их можно комбинировать:

CREATE INDEX articles_active_title_idx
  ON articles(LOWER(title))
  WHERE published_at IS NOT NULL;

Уникальные частичные индексы

Классический шаблон «одна активная строка»:

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

-- Same email can exist many times in deleted users, but only once active.

Предикат индекса должен соответствовать запросу

Планировщик использует частичный индекс только тогда, когда предложение WHERE в запросе «подразумевает» предикат индекса:

-- Index:  WHERE is_active = true
-- Match:  WHERE is_active = true AND user_id = 42       ✓
-- Match:  WHERE is_active AND user_id = 42             ✓
-- No:     WHERE user_id = 42                           ✗
-- No:     WHERE is_active IS NOT FALSE                 ✗ (logically same but planner may not realise)

Не используйте чрезмерно

Множество частичных индексов, охватывающих непересекающиеся подмножества, может замедлить запись (каждый INSERT должен обновлять все подходящие индексы). Используйте их выборочно для часто выполняемых запросов.

Итоги

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

Частичный индекс: индексируйте только запрашиваемое подмножество
Индекс по выражению: индексируйте вычисляемые значения
Для обоих требуются предикаты IMMUTABLE
Для соответствия запрос должен использовать тот же предикат

Быстрая проверка

Вам нужны регистронезависимые операции поиска по адресу электронной почты. Какой индекс будет наиболее эффективным?

Часто задаваемые вопросы

Урок «Частичные индексы и индексы по выражениям» бесплатный?

Да — полный текст урока «Частичные индексы и индексы по выражениям» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Частичные индексы и индексы по выражениям»?

Индексируйте только нужные строки с помощью предложений WHERE и индексируйте вычисляемые выражения (lower(email), date_trunc('day', ts)) Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «Частичные индексы и индексы по выражениям»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

Индексы B-tree, Hash, GiST и GIN
Составные индексы и порядок столбцов
Частичные индексы и индексы по выражениям
Обслуживание индексов и раздувание

← Назад к SQL Academy