11.4. Индексы и предложения ORDER BY
Помимо простого поиска строк для выдачи в результате запроса, индексы также могут применяться для сортировки строк в определённом порядке. Это позволяет учесть предложение ORDER BY
в запросе, не выполняя сортировку дополнительно. Из всех типов индексов, которые поддерживает Postgres Pro, сортировать данные могут только B-деревья — индексы других типов возвращают строки в неопределённом, зависящем от реализации порядке.
Планировщик может выполнить указание ORDER BY
, либо просканировав существующий индекс, подходящий этому указанию, либо просканировав таблицу в физическом порядке и выполнив сортировку явно. Для запроса, требующего сканирования большой части таблицы, явная сортировка скорее всего будет быстрее, чем применение индекса, так как при последовательном чтении она потребует меньше операций ввода/вывода. Важный особый случай представляет ORDER BY
в сочетании с LIMIT
n
: при явной сортировке системе потребуется обработать все данные, чтобы выбрать первые n
строк, но при наличии индекса, соответствующего столбцам в ORDER BY
, первые n
строк можно получить сразу, не просматривая остальные вовсе.
По умолчанию элементы B-дерева хранятся в порядке возрастания, при этом значения NULL идут в конце (для упорядочивания равных записей используется табличный столбец TID). Это означает, что при прямом сканировании индекса по столбцу x
порядок оказывается соответствующим указанию ORDER BY x
(или точнее, ORDER BY x ASC NULLS LAST
). Индекс также может сканироваться в обратную сторону, и тогда порядок соответствует указанию ORDER BY x DESC
(или точнее, ORDER BY x DESC NULLS FIRST
, так как для ORDER BY DESC
подразумевается NULLS FIRST
).
Вы можете изменить порядок сортировки элементов B-дерева, добавив уточнения ASC
, DESC
, NULLS FIRST
и/или NULLS LAST
при создании индекса; например:
CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST); CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
Индекс, в котором элементы хранятся в порядке возрастания и значения NULL идут первыми, может удовлетворять указаниям ORDER BY x ASC NULLS FIRST
или ORDER BY x DESC NULLS LAST
, в зависимости от направления просмотра.
У вас может возникнуть вопрос, зачем нужны все четыре варианта при создании индексов, когда и два варианта с учётом обратного просмотра покрывают все виды ORDER BY
. Для индексов по одному столбцу это и в самом деле излишне, но для индексов по многим столбцам это может быть полезно. Рассмотрим индекс по двум столбцам (x, y)
: он может удовлетворять указанию ORDER BY x, y
при прямом сканировании или ORDER BY x DESC, y DESC
при обратном. Но вполне возможно, что приложение будет часто выполнять ORDER BY x ASC, y DESC
. В этом случае получить такую сортировку от простого индекса нельзя, но можно получить подходящий индекс, определив его как (x ASC, y DESC)
или (x DESC, y ASC)
.
Очевидно, что индексы с нестандартными правилами сортировки весьма специфичны, но иногда они могут кардинально ускорить определённые запросы. Стоит ли вводить такие индексы, зависит от того, как часто выполняются запросы с необычным порядком сортировки.