Как создать индекс для поиска по статусу и дате создания заказа

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

Понимаете ли вы, в каком порядке класть колонки под конкретный запрос и как индекс работает с сортировкой. Следом уточняют, как вы выкатите такой индекс на таблицу с миллионами заказов, не остановив запись.

Как отвечать

Сначала попросите сам запрос. Типичный вид: заказы в статусе NEW за период, новые сверху, страницами. Под него подходит индекс (status, created_at):

• статус первым: по нему равенство, это сужает поиск до одного участка дерева;
• дата второй: по ней диапазон, а внутри одного статуса записи уже упорядочены по времени;
• сортировка бесплатно: при WHERE status = ? ORDER BY created_at DESC LIMIT 50 база читает участок индекса с конца и останавливается на пятидесятой строке, без сортировки;
• частичный индекс: если ищут только по «живым» статусам, а архивных заказов подавляющее большинство, CREATE INDEX ... ON orders (created_at) WHERE status IN ('NEW', 'PAID') будет в разы меньше. Условие в запросе должно укладываться в условие индекса, например status = 'NEW', иначе планировщик индекс не возьмёт.

Дальше про выкатку. В PostgreSQL обычный CREATE INDEX блокирует запись в таблицу на всё время построения, поэтому на проде нужен CREATE INDEX CONCURRENTLY. Внутри транзакции он не работает, и в Flyway или Liquibase такую миграцию нужно явно вывести из транзакции. После построения проверьте EXPLAIN (ANALYZE, BUFFERS) на реальном запросе.

Со стороны Java: в Spring Data передавайте период диапазоном дат, а для глубоких страниц вместо OFFSET используйте keyset-пагинацию по (created_at, id).

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

Ставят дату первой. Тогда запрос по статусу за период просматривает весь диапазон дат и отсеивает статусы уже при чтении индекса, выигрыш намного меньше.

Пишут WHERE DATE(created_at) = ? или приводят тип колонки в условии. Функция над колонкой отключает обычный индекс, правильно created_at >= :from AND created_at < :to.

Делают отдельный индекс только по status. У колонки с пятью значениями селективность низкая, и база чаще выберет полный просмотр таблицы.