Почему в составном индексе важен порядок колонок?

Почему порядок важен. B-дерево по (a, b) хранит пары, упорядоченные по a, а при равных a — по b. Найти все строки с a = 5 — один диапазон в индексе. Найти все строки с b = 7 — значения разбросаны по всему индексу, и база выберет полный просмотр (PostgreSQL иногда сканирует весь индекс или использует skip scan в новых версиях, но это дорого).

Как выбирать порядок:

• колонки, которые сравниваются на равенство, — первыми;
• затем колонка диапазона (created_at > ...) или сортировки — после диапазонной колонки следующие колонки уже не сужают поиск;
• смотреть на реальные запросы: один индекс (user_id, status, created_at) может обслужить несколько запросов, начинающихся с user_id;
• при прочих равных — более селективные колонки раньше.

Покрывающий индекс — включает все колонки, нужные запросу (INCLUDE в PostgreSQL), и запрос выполняется только по индексу (Index Only Scan).

Лишние индексы: индекс (a) не нужен, если есть (a, b) — левый префикс покрывает поиск по a.

CREATE INDEX ON orders (customer_id, created_at DESC);
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;  -- индекс целиком
SELECT * FROM orders WHERE created_at > now() - interval '1 day';               -- этот индекс не подходит