Оптимизировать часто выполняемый запрос к таблице orders

70. Оптимизировать частый запрос заказов пользователя

Условие задачи:
В системе часто выполняется запрос к таблице orders. При его выполнении наблюдается высокая нагрузка на диск.

Необходимо объяснить, как оптимизировать запрос и уменьшить количество операций чтения.

Код:

SELECT
    status,
    created_at
FROM orders
WHERE user_id = :userId
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 20;

Спойлеры к решению

Подсказки

💡 Индекс должен учитывать одновременно фильтрацию по user_id, диапазон по created_at и сортировку по дате.
💡 Здесь подходит составной индекс (user_id, created_at DESC).
💡 Благодаря LIMIT 20 СУБД сможет остановить чтение после нахождения первых подходящих строк.
💡 В PostgreSQL можно добавить status через INCLUDE, чтобы создать покрывающий индекс.
💡 Реальный эффект нужно проверить через EXPLAIN (ANALYZE, BUFFERS).

Решение

Основная оптимизация — создать составной индекс:

CREATE INDEX idx_orders_user_created_at
    ON orders (user_id, created_at DESC);

Такой индекс хорошо соответствует запросу:

user_id = ...         → точное условие
created_at >= ...     → диапазон
ORDER BY created_at   → порядок внутри user_id
LIMIT 20              → нужны только первые 20 строк

СУБД может быстро найти записи конкретного пользователя и читать их начиная с самых свежих, вместо сканирования большого количества строк и отдельной сортировки.

Для PostgreSQL можно сделать покрывающий индекс:

CREATE INDEX idx_orders_user_created_at_cover
    ON orders (user_id, created_at DESC)
    INCLUDE (status);

Все данные, необходимые запросу, тогда находятся в индексе:

user_id;
created_at;
status.

Это создаёт возможность для Index Only Scan, то есть чтения результата непосредственно из индекса без обращения к основной таблице.

Однако Index Only Scan не гарантирован: в PostgreSQL он также зависит от состояния visibility map и активности изменений в таблице.

Проверять результат оптимизации лучше так:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    status,
    created_at
FROM orders
WHERE user_id = :userId
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 20;

Стоит обратить внимание на:

Index Scan или Index Only Scan;
отсутствие лишней операции Sort;
количество прочитанных страниц (shared read);
фактическое количество обработанных строк;
время выполнения.

Если таблица очень большая, дополнительно можно рассмотреть партиционирование по created_at, особенно если большинство запросов работают только с ограниченным диапазоном дат. Но начинать оптимизацию следует именно с подходящего составного индекса и проверки плана выполнения.