11.4. Индексы и ORDER BY#
11.4. Индексы и ORDER BY
#
В дополнение к простому поиску строк, которые должны быть возвращены запросом,
индекс может быть способен доставить их в определенном упорядоченном порядке.
Это позволяет учитывать спецификацию ORDER BY
запроса
без отдельного шага сортировки. Из типов индексов, которые в настоящее время
поддерживаются Tantor BE, только B-дерево
может производить отсортированный вывод - другие типы индексов возвращают
соответствующие строки в произвольном порядке, зависящем от реализации.
Планировщик будет рассматривать удовлетворение спецификации ORDER BY
либо путем сканирования доступного индекса, соответствующего спецификации, либо путем сканирования таблицы в физическом порядке и выполнения явной сортировки. Для запроса, требующего сканирования большой части таблицы, явная сортировка, скорее всего, будет быстрее, чем использование индекса, потому что она требует меньшего количества операций ввода-вывода на диск из-за последовательного доступа к данным. Индексы более полезны, когда нужно извлечь только несколько строк. Важный особый случай - ORDER BY
в сочетании с LIMIT
n
: явная сортировка должна обработать все данные, чтобы определить первые n
строк, но если есть индекс, соответствующий ORDER BY
, первые n
строк могут быть получены напрямую, без сканирования остальных данных.
По умолчанию индексы B-дерева хранят свои записи в порядке возрастания с последними значениями NULL (идентификатор строки таблицы используется для упорядочивания столбцов при равных значениях). Это означает, что прямой проход по индексу на столбце x
выдает результат, удовлетворяющий ORDER BY x
(или более подробно, ORDER BY x ASC NULLS LAST
). Индекс также может быть просканирован в обратном порядке, производя вывод, удовлетворяющий ORDER BY x DESC
(или более подробно, ORDER BY x DESC NULLS FIRST
, так как NULLS FIRST
является значением по умолчанию для ORDER BY DESC
).
Вы можете настроить порядок сортировки индекса 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)
.
Очевидно, индексы с нестандартным порядком сортировки являются достаточно специализированной функцией, но иногда они могут значительно ускорить выполнение определенных запросов. Стоит поддерживать такой индекс только в том случае, если вы часто используете запросы, требующие специального порядка сортировки.