junior

Что такое составные индексы и как они работают?

Составной (композитный) индекс — индекс, построенный по нескольким столбцам. Он эффективен, когда запросы фильтруют по комбинации столбцов, но работает по правилу левого префикса.

Пример

CREATE INDEX idx_tx_account_date ON transactions (account_id, created_at);

Правило левого префикса

Составной B-tree индекс эффективно используется, если в условии запроса участвуют столбцы, начиная с первого (левого) столбца индекса.

Пример

-- Индекс: (account_id, created_at)

-- Использует индекс (оба столбца):
SELECT * FROM transactions
WHERE account_id = 100 AND created_at > '2024-01-01';

-- Использует индекс (только первый столбец):
SELECT * FROM transactions
WHERE account_id = 100;

-- НЕ использует индекс (нет первого столбца):
SELECT * FROM transactions
WHERE created_at > '2024-01-01';

Порядок столбцов

Столбцы с высокой селективностью (больше уникальных значений) ставятся первыми
Столбцы для фильтрации по равенству — перед столбцами для диапазонного поиска

Пример

-- Оптимальный порядок для запроса:
-- WHERE status = 'ACTIVE' AND created_at BETWEEN ... AND ...
CREATE INDEX idx_tx_status_date ON transactions (status, created_at);

Покрывающий индекс (Index Only Scan)

Пример

-- Если запрос выбирает только столбцы из индекса,
-- PostgreSQL может ответить, не обращаясь к таблице
CREATE INDEX idx_covering ON transactions (account_id, created_at, amount);

-- Index Only Scan:
SELECT created_at, amount FROM transactions WHERE account_id = 100;

Начиная с PostgreSQL 11 можно использовать INCLUDE для добавления столбцов в индекс без влияния на поисковые ключи:

Пример

CREATE INDEX idx_tx_cover ON transactions (account_id) INCLUDE (amount, status);

На собеседовании: правило левого префикса — обязательный вопрос. Объясните его через аналогию с телефонной книгой: можно найти «Иванов, Пётр», можно найти всех «Ивановых», но нельзя найти всех «Петров» — для этого нужен отдельный индекс.

#postgresql