AI Как один индекс на created_at сократил время ответа API с 12 секунд до 40 мс

[AI]

Неделю назад прилетел тикет: «Страница заказов грузится вечность». Открыл — действительно, 12 секунд на первую загрузку. На проде. С реальными пользователями.

Спойлер: проблема была в том месте, куда я смотрел последним.

Что имеем


Типичный интернет-магазин на Django + PostgreSQL. Админка, где менеджеры смотрят список заказов. Таблица orders — примерно 800 тысяч записей, растёт на 2-3 тысячи в день.

Запрос, который дёргает страница:

SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;


Казалось бы, что тут сложного? LIMIT 50, да ещё и с фильтром по статусу.

Первые подозрения


Открыл EXPLAIN ANALYZE. Результат:

Seq Scan on orders (cost=0.00..45892.00 rows=234521 width=312)
Filter: (status = 'pending')
Rows Removed by Filter: 567842


Ага, Seq Scan. Полный перебор 800 тысяч строк, чтобы выбрать 50.

Ладно, думаю, добавлю индекс на status:

CREATE INDEX idx_orders_status ON orders(status);


Запускаю снова. Время упало до 4 секунд. Лучше, но всё равно много.

Где собака зарыта


Смотрю план ещё раз:

Index Scan using idx_orders_status on orders
Index Cond: (status = 'pending')
Sort: ...created_at DESC


Вот оно. PostgreSQL находит 230 тысяч записей со статусом pending, потом сортирует их все по дате, и только потом берёт первые 50.

Проблема не в фильтрации. Проблема в сортировке.

Решение


Составной индекс. Причём порядок полей — от этого зависит всё:

CREATE INDEX idx_orders_status_created
ON orders(status, created_at DESC);


Почему именно так? PostgreSQL сможет пройти по индексу уже в нужном порядке. Сначала фильтрует по status, потом идёт по created_at — и останавливается, как только набрал 50 строк.

Результат:

Index Scan using idx_orders_status_created on orders
Index Cond: (status = 'pending')
Rows: 50
Actual Time: 0.04..0.08 ms


40 миллисекунд. Не 12 секунд, не 4 секунды. 40 мс.

Почему я не сделал это сразу


Честно говоря, привык думать об индексах как о чём-то для WHERE. Забыл, что ORDER BY + LIMIT — это отдельная история. База может найти миллион подходящих строк за секунду, но если их надо отсортировать в памяти — привет, тормоза.

Второй момент: порядок полей в составном индексе. (created_at, status) работал бы хуже, потому что сначала пришлось бы сканировать по дате, а уже потом фильтровать по статусу.

Проверка на проде


Добавил индекс в миграцию с CONCURRENTLY, чтобы не блокировать таблицу:

CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders(status, created_at DESC);


На 800 тысяч записей создание заняло около 40 секунд. Таблица всё это время была доступна.

После деплоя страница открывается мгновенно. Менеджеры довольны, тикет закрыт.

Что вынес

1. 
   EXPLAIN ANALYZE — всегда. Не гадать, а смотреть план.
   
   
2. 
   Составные индексы для запросов с WHERE + ORDER BY. Порядок полей: сначала то, по чему фильтруем, потом то, по чему сортируем.
   
   
3. 
   LIMIT не спасает, если сортировка идёт после фильтрации. База должна сначала найти все подходящие строки.
   
   
4. 
   CREATE INDEX CONCURRENTLY — иначе таблица блокируется на время создания индекса.
   

Мелочь, одна строчка в миграции. А пользователи ждали по 12 секунд.


Если сталкивались с похожим — пишите в комментариях. Интересно, какие ещё неочевидные случаи бывают с индексами.