Краткий ответ

LIMIT 20 формирует у оптимизатора цель быстро получить первые строки, а не обработать весь результат. Поэтому PostgreSQL может выбрать индекс по created_at: он сразу выдаёт строки в нужном порядке и останавливается после 20 подходящих, даже если до них приходится проверить много записей.

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

Исторический контекст

Реляционная модель описывает полный набор строк результата, но прикладным системам часто нужны только первые строки: например, первые элементы списка или результаты поиска на первой странице. Поэтому оптимизаторы научились учитывать Top-N, LIMIT и другие формы ограничения результата при выборе плана.

Без этой оптимизации запрос с ORDER BY мог бы сначала отфильтровать все подходящие строки, полностью отсортировать их, а затем вернуть первые 20. Использование упорядоченного индекса позволяет заменить такую работу ранней остановкой, если стоимость доступа к первым строкам действительно мала.

Постановка проблемы

Индекс по created_at идеально поддерживает ORDER BY, но не обязательно эффективно поддерживает WHERE status = 'paid'. При чтении индекса PostgreSQL может обращаться к строкам в порядке даты, проверять их статус и отбрасывать неподходящие записи.

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

Подробное решение

PostgreSQL учитывает ограничение результата через оценку доли строк, которую требуется получить; это часто называют row goal. При наличии LIMIT стоимость плана оценивается не только для полного результата, но и для достижения первых строк.

Индекс ix_orders_created_at позволяет читать записи сразу в порядке created_at DESC. Отдельный индекс ix_orders_status фильтрует статус, но обычно не выдаёт строки в требуемом порядке, поэтому после его использования потребуется сортировка либо дополнительная операция соединения с таблицей.

Для одновременной фильтрации и сортировки обычно лучше подходит составной индекс:

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

SELECT id, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Сначала индекс ограничивает диапазон значением status, затем читает его по created_at. В результате можно получить первые 20 строк без просмотра большого числа записей других статусов и без отдельной сортировки.

Если запрос всегда ищет только оплаченные заказы, возможен частичный индекс:

CREATE INDEX ix_orders_paid_date_partial
    ON orders (created_at DESC)
    WHERE status = 'paid';

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

Оценку нужно проверять через EXPLAIN (ANALYZE, BUFFERS). Особенно важны фактическое число строк, количество отброшенных фильтром записей и объём чтения. Если фактические значения сильно отличаются от оценок, следует проверить статистику и распределение данных, но одно лишь обновление статистики не заменяет подходящую структуру индекса.

Ситуация из практики

В API-методе истории заказов запрос возвращал последние 20 оплаченных заказов. План с индексом по created_at выглядел логично: он избегал сортировки, но на тестовых данных с равномерным распределением работал хорошо. В рабочей базе оплаченные заказы были редкими и часто располагались далеко от начала индексного прохода, поэтому EXPLAIN ANALYZE показал большое число строк, отброшенных по status.

Рассматривались три варианта. Индекс только по status хорошо сокращал выборку, но требовал сортировки; индекс только по created_at сохранял порядок, но плохо фильтровал; составной индекс (status, created_at DESC) поддерживал обе части запроса, однако занимал больше места и увеличивал стоимость вставок и обновлений.

Выбрали составной индекс, поскольку запрос был частым, а порядок и фильтр были стабильными. После проверки плана чтение стало ограничиваться нужным диапазоном статуса, а отдельная сортировка и массовая фильтрация исчезли. Для редко используемых статусов такой индекс не обязательно оправдан: его нужно оценивать по рабочей нагрузке, размеру таблицы и стоимости поддержки.

Что кандидаты часто упускают

Вопрос: Всегда ли удаление LIMIT приведёт к выбору индекса по status?

Ответ: Нет. Без LIMIT исчезает преимущество ранней остановки, поэтому индекс по статусу может стать привлекательнее, но итог зависит от селективности, стоимости сортировки, размера таблицы и оценок оптимизатора. Если большинство строк имеют статус paid, полное сканирование с сортировкой или последовательное чтение может оказаться дешевле любого индексного плана.
Вопрос: Почему индекс (created_at DESC, status) не равнозначен индексу (status, created_at DESC) для этого запроса?

Ответ: В индексе сначала упорядочены значения created_at, а status является вторичным ключом внутри одинаковых дат. Условие по второму ключу обычно не позволяет быстро перейти к компактному диапазону всех оплаченных строк: приходится просматривать много дат и проверять статус. В варианте (status, created_at DESC) равенство по первому столбцу сразу ограничивает диапазон, а второй сохраняет нужный порядок внутри него.
Вопрос: Почему глубокий OFFSET может свести преимущество такого плана на нет?

Ответ: При OFFSET 100000 LIMIT 20 сервер должен найти и пропустить первые 100000 строк результата, прежде чем вернуть следующие 20. Даже при составном индексе это означает чтение и обработку большого числа индексных записей, а иногда и обращение к таблице. Для последовательной навигации обычно эффективнее использовать keyset pagination, например условие created_at < :last_seen_date с дополнительным уникальным ключом для устранения неоднозначности сортировки.