В каком порядке строить составной индекс для user_id и даты

Что проверяют

Понимаете ли вы правило «сначала равенство, потом диапазон». Это самый частый практический случай составного индекса: выборка записей пользователя за период.

Как отвечать

Для типичного запроса вида «события пользователя за период, новые сверху» правильный порядок это (user_id, created_at).

Причина в устройстве индекса: записи упорядочены сначала по первому полю, затем по второму. Условие равенства по user_id сразу сужает поиск до непрерывного диапазона, внутри которого даты уже отсортированы, поэтому фильтр по периоду и сортировка берутся из индекса без дополнительной работы.

create index on events (user_id, created_at desc);
select * from events
where user_id = $1 and created_at >= $2
order by created_at desc
limit 50;

Обратный порядок (created_at, user_id) для такого запроса почти бесполезен: строки одного пользователя разбросаны по всему диапазону дат, и база прочитает лишнее. Он подойдёт другой задаче, например выборке всех событий за сутки без фильтра по пользователю.

Что стоит добавить:

• направление сортировки: если выдача всегда идёт по убыванию даты, desc прямо в индексе избавляет от отдельного шага сортировки;
• покрывающий индекс: добавив в include пару колонок, которые возвращает запрос, можно ответить, не заглядывая в таблицу;
• частичный индекс: если читают только неархивные записи, where is_active уменьшает индекс в разы;
• проверка: план через EXPLAIN (ANALYZE, BUFFERS) покажет, использован ли индекс и сколько строк отброшено уже после чтения.

Где спотыкаются

Создают два отдельных индекса вместо одного составного и надеются на их пересечение. База так умеет, но это заметно дороже.

Ставят диапазонное поле первым и теряют пользу второй колонки.

Забывают, что при пагинации через большой OFFSET индекс уже не спасает, нужна пагинация по ключу.