re:re:actable
Урок 053 минуты чтения

Как индекс помогает 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 — в определении индекса.

Документация

PostgreSQL: Indexes and ORDER BY.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам