Складений індекс — це B-tree, у якому записи відсортовані спершу по першій колонці, всередині рівних значень по другій, і так далі. Звідси правило лівого префікса: індекс (a, b, c) дає швидкий пошук по a, по a, b і по a, b, c, але не по b окремо, бо значення b розкидані по всьому дереву. Телефонний довідник відсортований за прізвищем, потім за імʼям; знайти всіх Олен у ньому неможливо без повного перегляду.
Порядок колонок визначається запитами, які індекс має обслуговувати. Колонки з умовою рівності йдуть першими, колонка з діапазоном або та, по якій потрібне сортування, останньою. Після діапазону решта індексу для звуження пошуку вже не працює. Для типового WHERE user_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20 правильний індекс (user_id, status, created_at): база стрибає в потрібне місце й читає двадцять уже відсортованих записів.
Індекс, який містить усі колонки запиту, стає покривним: таблиця не читається взагалі, у PostgreSQL це Index Only Scan із INCLUDE для додаткових колонок. Ціна кожного індексу — місце й повільніший запис, тому один складений індекс, спроєктований під кілька запитів, зазвичай кращий за набір вузьких. Після створення індексу його використання перевіряють через EXPLAIN, а не припускають.
-- Запит, під який проєктуємо індекс
SELECT id, total
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
-- Правильно: рівності попереду, сортування останнім
CREATE INDEX orders_user_status_created ON orders (user_id, status, created_at DESC);
-- Неправильно: після діапазону або сортування решта індексу не використовується для пошуку
CREATE INDEX orders_created_user ON orders (created_at, user_id, status);
-- Покривний: total у INCLUDE, таблицю читати не треба (PostgreSQL)
CREATE INDEX orders_user_status_created_cov
ON orders (user_id, status, created_at DESC) INCLUDE (total);
-- Перевірка: має бути Index Scan / Index Only Scan, а не Seq Scan + Sort
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;Хороша ілюстрація — телефонний довідник: за прізвищем шукати легко, за одним лише імʼям — ні. І назвіть правило: рівність, потім діапазон або сортування.