Как индекс помогает ORDER BY
На этой странице
B-tree хранит ключи отсортированными, поэтому, если порядок индекса совпадает с ORDER BY, PostgreSQL читает строки уже в нужном порядке и пропускает шаг Sort. Вместе с LIMIT это даёт главный выигрыш: база читает первые N записей индекса и останавливается, а не сортирует миллион строк ради 20. Индекс можно читать в обратном направлении, поэтому (created_at) подходит и для ASC, и для DESC. Для фильтра + сортировки нужен составной индекс: сначала столбцы равенства из WHERE, затем столбцы ORDER BY — (user_id, created_at DESC) для WHERE user_id = $1 ORDER BY created_at DESC. Смешанные направления (a ASC, b DESC) требуют индекса с теми же направлениями.
Вопрос
Как индекс помогает ORDER BY и как подобрать индекс под сортировку?
Без индекса
EXPLAIN ANALYZE
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 20;
-- Limit
-- -> Sort (Sort Method: top-N heapsort)
-- -> Seq Scan on posts (rows=1000000)С индексом
CREATE INDEX posts_created_at_idx ON posts (created_at);
-- Limit
-- -> Index Scan Backward using posts_created_at_idx on posts (actual rows=20)Backward — индекс читается с конца, отдельный индекс DESC не нужен.
Фильтр + сортировка
SELECT id, total FROM orders
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20;
CREATE INDEX orders_user_created_idx ON orders (user_id, created_at DESC);Внутри одного user_id записи индекса упорядочены по created_at — поиск и сортировка за один проход.
| Индекс | Помогает |
|---|---|
(user_id, created_at) | ✓ равенство + сортировка |
(created_at, user_id) | Частично: обход по времени с фильтрацией, плохо для пользователей с малым числом заказов |
(user_id) | Поиск, но потом Sort |
Диапазон + сортировка
WHERE status IN ('new', 'paid') ORDER BY created_at -- IN — несколько значений
WHERE created_at > $1 ORDER BY total -- диапазон по одному столбцу, сортировка по другомуПосле диапазонного условия или IN порядок по следующему столбцу индекса уже не глобальный, и может понадобиться Sort (иногда планировщик использует Merge Append).
Смешанные направления
-- ORDER BY category_id ASC, price DESC
CREATE INDEX products_cat_price_idx ON products (category_id ASC, price DESC);Индекс (category_id, price) подойдёт для ASC, ASC и (в обратном чтении) DESC, DESC, но не для ASC, DESC.
NULLS
-- ORDER BY rating DESC NULLS LAST
CREATE INDEX products_rating_idx ON products (rating DESC NULLS LAST);По умолчанию DESC подразумевает NULLS FIRST; если запрос требует NULLS LAST, порядок индекса должен совпадать.
Keyset-пагинация
SELECT id, title, created_at FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;
CREATE INDEX posts_created_id_idx ON posts (created_at DESC, id DESC);Каждая страница — это поиск в индексе и чтение 20 записей, независимо от глубины. Подробнее — пагинация.
Частые ошибки
- Индекс на столбец сортировки, но фильтр по другому столбцу идёт первым в запросе.
- Порядок столбцов в составном индексе «сортировка, затем фильтр».
- Смешанные направления без соответствующего индекса.
ORDER BY lower(title)при индексе поtitle.
Что важно помнить
- Индекс с правильным порядком убирает
Sort. ORDER BY ... LIMIT+ индекс = чтение только N записей.- Столбцы равенства → столбцы сортировки.
Мини-шпаргалка
(filter_eq_cols, order_cols).Index Scan Backward— обратный обход.(a ASC, b DESC)— для смешанной сортировки.NULLS LAST— в определении индекса.