Оптимизировать запрос к таблице orders

76. Оптимизировать запрос к большой таблице orders

Условие задачи:
В PostgreSQL есть таблица orders, содержащая миллионы записей.

Запрос:

фильтрует заказы по status;
выбирает записи за заданный диапазон дат;
сортирует их по created_at в порядке убывания;
возвращает первые 100 строк.

Необходимо определить причину медленной работы запроса и предложить оптимизацию.

Код:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    status VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL
);

SELECT *
FROM orders
WHERE status = 'COMPLETED'
  AND created_at BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY created_at DESC
LIMIT 100;

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

Подсказки

💡 Для начала проверь план выполнения через EXPLAIN (ANALYZE, BUFFERS).
💡 Индекс должен учитывать точное условие по status и диапазон по created_at.
💡 Составной индекс (status, created_at DESC) также помогает получить строки сразу в нужном порядке.
💡 Для диапазона по TIMESTAMP лучше использовать полуоткрытый интервал: >= начало периода и < начало следующего периода.
💡 Если запросы почти всегда выполняются только для status = 'COMPLETED', можно рассмотреть частичный индекс.

Решение

Для такого запроса подходит составной индекс:

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

В индексе сначала идёт status, по которому используется точное сравнение:

status = 'COMPLETED'

После него расположен created_at, по которому одновременно выполняются диапазонная фильтрация и сортировка:

created_at >= ...
created_at < ...
ORDER BY created_at DESC

Благодаря этому PostgreSQL может читать подходящие строки уже в нужном порядке и остановиться после получения первых 100 записей.

Сам запрос лучше записать через полуоткрытый диапазон:

SELECT *
FROM orders
WHERE status = 'COMPLETED'
  AND created_at >= TIMESTAMP '2024-01-01'
  AND created_at < TIMESTAMP '2024-02-01'
ORDER BY created_at DESC
LIMIT 100;

Исходное условие:

created_at BETWEEN '2024-01-01' AND '2024-01-31'

для поля TIMESTAMP эквивалентно верхней границе около:

2024-01-31 00:00:00

поэтому записи позднее полуночи 31 января не попадут в результат.

Если запросы часто выполняются именно для COMPLETED, можно использовать более компактный частичный индекс:

CREATE INDEX idx_orders_completed_created_at
    ON orders (created_at DESC)
    WHERE status = 'COMPLETED';

Он содержит только завершённые заказы и поэтому может занимать меньше места и требовать меньше чтений.

Проверить результат оптимизации можно так:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE status = 'COMPLETED'
  AND created_at >= TIMESTAMP '2024-01-01'
  AND created_at < TIMESTAMP '2024-02-01'
ORDER BY created_at DESC
LIMIT 100;

В плане стоит проверить:

используется ли Index Scan;
отсутствует ли лишняя операция Sort;
сколько строк реально просматривается до получения LIMIT 100;
сколько страниц пришлось прочитать с диска.

Основная оптимизация для этого запроса — индекс (status, created_at DESC) либо частичный индекс по created_at для status = 'COMPLETED'.