Составные индексы: правила проектирования и типичные ошибки!
Составные индексы используются для ускорения запросов по нескольким колонкам, но порядок полей внутри индекса напрямую влияет на его эффективность.
Представим таблицу заказов, где часто выполняются запросы по пользователю и дате создания заказа.
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
status VARCHAR(30),
created_at TIMESTAMP NOT NULL,
amount NUMERIC(10,2)
);
Обычный индекс на каждую колонку не всегда является оптимальным решением. СУБД может использовать несколько индексов одновременно, но такой план не всегда будет эффективнее одного правильно спроектированного составного индекса.
CREATE INDEX idx_orders_user
ON orders(user_id);
CREATE INDEX idx_orders_created_at
ON orders(created_at);
Для частого сценария поиска заказов конкретного пользователя за период лучше создать составной индекс:
CREATE INDEX idx_orders_user_created_at
ON orders(user_id, created_at);
Такой индекс эффективно работает для запросов, где используется первая колонка индекса или полный набор колонок.
SELECT *
FROM orders
WHERE user_id = 42
AND created_at >= '2026-01-01';
В B-tree индексе данные сначала сортируются по
user_id, а внутри одинаковых значений
user_id — по
created_at.
Поэтому СУБД быстро находит записи пользователя и затем выполняет поиск по диапазону дат. Также индекс будет использоваться:
SELECT *
FROM orders
WHERE user_id = 42;
Но он плохо подходит для поиска только по второй колонке:
SELECT *
FROM orders
WHERE created_at >= '2026-01-01';
Причина в структуре B-tree: данные сначала организованы по первой колонке индекса.
Для такого запроса отдельный индекс по дате будет более подходящим:
CREATE INDEX idx_orders_created_at
ON orders(created_at);
Ещё одна распространённая ошибка — добавление большого количества колонок в индекс.
CREATE INDEX idx_orders_all_columns
ON orders(user_id, status, created_at, amount);
Каждый дополнительный столбец увеличивает размер индекса и стоимость операций записи.
Перед созданием индекса нужно анализировать реальные запросы приложения, а не добавлять поля на всякий случай.
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 42
AND status = 'paid'
ORDER BY created_at DESC;
Для такого запроса может быть эффективнее индекс, учитывающий фильтрацию и сортировку:
CREATE INDEX idx_orders_user_status_created_at
ON orders(user_id, status, created_at DESC);
Правильно спроектированный составной индекс уменьшает количество операций чтения, снижает нагрузку на CPU и помогает оптимизатору выбрать более дешёвый план выполнения.
🔥 Главное правило простое: индекс создаётся под реальные запросы приложения, а не просто под структуру таблицы.
➡️
SQL Ready | #практика 
