Неделю назад прилетел тикет: «Страница заказов грузится вечность». Открыл — действительно, 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 секунд.

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