middle
Что такое частичные индексы и индексы на выражениях?
Частичный индекс (partial index) строится только для части строк таблицы, а индекс на выражении — на результате функции. Оба подхода позволяют создавать компактные и целевые индексы вместо тяжёлых полных.
Частичный индекс
Пример
-- Индекс только по активным транзакциям
CREATE INDEX idx_tx_active ON transactions (account_id)
WHERE status = 'PENDING';
-- Значительно меньше обычного индекса, быстрее обновляется
-- Используется только если в запросе совпадает условие:
SELECT * FROM transactions
WHERE account_id = 100 AND status = 'PENDING'; -- использует idx_tx_active
Типичные применения частичных индексов
-- Индекс только по необработанным записям
CREATE INDEX idx_queue_unprocessed ON task_queue (created_at)
WHERE processed = false;
-- Уникальность только для активных записей
CREATE UNIQUE INDEX idx_unique_active_email ON users (email)
WHERE is_deleted = false;
-- Индекс исключая NULL
CREATE INDEX idx_tx_reference ON transactions (reference_number)
WHERE reference_number IS NOT NULL;
Индекс на выражении (expression index)
Индекс, построенный на результате функции или выражения, а не на значении столбца напрямую.
Пример
-- Индекс для регистронезависимого поиска
CREATE INDEX idx_users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'user@bank.ru';
-- Индекс на дату из timestamp
CREATE INDEX idx_tx_date ON transactions (date(created_at));
SELECT * FROM transactions WHERE date(created_at) = '2024-06-15';
-- Индекс на извлечённое значение JSONB
CREATE INDEX idx_meta_type ON transactions ((metadata ->> 'type'));
SELECT * FROM transactions WHERE metadata ->> 'type' = 'transfer';
Запрос должен содержать точно такое же выражение, которое использовалось при создании индекса, иначе оптимизатор не сможет его применить.
На собеседовании: частичные индексы — это «серебряная пуля» для таблиц-очередей, где 99% строк обработаны и только 1% актуальны. Индекс на 1% строк вместо 100% — разница в размере и скорости обновления на порядки.