11.4. Индексы и ORDER BY#

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).

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