Как создать частичный индекс в PostgreSQL: условия и синтаксис?

Как создать частичный индекс в PostgreSQL: условия и синтаксис?

• Частичный индекс — это индекс, который охватывает только строки, соответствующие заданному условию.
• При создании такого индекса данные отбираются с помощью WHERE.
• Синтаксис в PostgreSQL: CREATE INDEX имя_индекса ON таблица(колонка) WHERE условие;
• Он повышает производительность запросов с фильтрацией и уменьшает размер индекса.
• Подход применяется, если в запросах регулярно используется только часть строк таблицы.
• Частичный индекс сокращает расход ресурсов и ускоряет выборку по указанному условию.
• Пример: CREATE INDEX idx_active ON users(email) WHERE active = true; — индекс создаётся только для активных пользователей, благодаря чему запросы по email выполняются быстрее.

Подробный ответ

Основной ответ

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

CREATE INDEX имя_индекса ON имя_таблицы (колонка)
WHERE условие;

Условие, указанное после WHERE, определяет, какие строки попадут в индекс.

Ключевые моменты

• Повышение производительности: Частичный индекс быстрее работает и требует меньше места, когда запросы часто используют фильтр, совпадающий с его условием.
• Пример условия: можно включить в индекс только «активные» записи, например WHERE status = 'active'.
• Поддержка всех типов индексов: частичными могут быть B-tree, GiST, GIN, BRIN и другие индексы.
• Использование планировщиком: PostgreSQL выбирает частичный индекс автоматически, если условие запроса покрывает условие, заданное для индекса.

Практический контекст

Частичные индексы обычно применяют в таблицах с большим объёмом данных, когда большинство запросов обращается лишь к определённой части строк — например, к текущим активным пользователям, а не к архивным. В PostgreSQL 14+ частичные индексы поддерживаются наряду с расширенными возможностями обхода индексов, предназначенными для оптимизации запросов.