11.3. Многоколоночные индексы#
11.3. Многоколоночные индексы #
Индекс может быть определен на нескольких столбцах таблицы. Например, если у вас есть таблица следующего вида:
CREATE TABLE test2 ( major int, minor int, name varchar );
(скажем, вы храните свой /dev
каталог в базе данных...) и часто выполняете запросы вроде:
SELECT name FROM test2 WHERE major =constantAND minor =constant;
Если это целесообразно, то можно определить индекс на столбцах major и minor вместе, например:
CREATE INDEX test2_mm_idx ON test2 (major, minor);
В настоящее время только типы индексов B-дерево, GiST, GIN и BRIN поддерживают индексы с несколькими ключевыми столбцами. Возможность использования нескольких ключевых столбцов не зависит от того, можно ли добавлять столбцы INCLUDE в индекс. Индексы могут содержать до 32 столбцов, включая столбцы INCLUDE. (Этот предел можно изменить при сборке Tantor SE; см. файл pg_config_manual.h).
Составной индекс B-tree можно использовать в условиях запроса, с любым подмножеством столбцов индекса, но индекс работает наиболее эффективно, когда на ведущие (левые) столбцы наложены ограничения. Точное правило заключается в том, что ограничения равенства с ведущими столбцами, а также любые ограничения неравенства с первым столбцом, для которого не задано ограничение равенства, всегда используются для ограничения сканируемой области индекса. Ограничения на столбцы, находящиеся правее них, проверяются в индексе, поэтому они всегда позволяют избежать обращений к таблице, но не обязательно уменьшают сканируемую область индекса. Если при сканировании индекса B-tree можно эффективно применить оптимизацию skip scan, то при навигации по индексу будут применяться ограничения по каждому столбцу во время повторных поисков по индексу. Это может уменьшить размер области индекса, которую необходимо прочитать, даже если для одного или нескольких столбцов (расположенных перед наименее значимым столбцом индекса из предиката запроса) отсутствует обычное ограничение равенства. Skip scan работает путем внутреннего создания динамического ограничения равенства, которое соответствует любому возможному значению в столбце индекса (применяется только для столбцов без ограничений равенства из предиката запроса, и только если сгенерированное ограничение может быть использовано вместе с последующим ограничением по столбцу из предиката запроса).
Например, если имеется индекс по столбцам (x, y) и в условии запроса указано WHERE y = 7700, то сканирование индекса B-дерева может применяться оптимизацию skip scan. Обычно это происходит, когда планировщик запросов ожидает, что повторяющиеся поиски WHERE x = N AND y = 7700 для каждого возможного значения N (или для каждого значения x, фактически хранящегося в индексе) являются самым быстрым возможным подходом с учётом доступных индексов в таблице. Такой подход обычно применяется только тогда, когда количество уникальных значений x настолько мало, что планировщик ожидает, что сканирование пропустит большую часть индекса (так как большинство его листовых страниц не могут содержать подходящих кортежей). Если же уникальных значений x много, то придётся просканировать весь индекс, поэтому в большинстве случаев планировщик выберет последовательное сканирование таблицы, а не использование индекса.
Оптимизация skip scan также может применяться выборочно, во время сканирования B-дерева, если в предикате запроса присутствуют полезные ограничения. Например, при наличии индекса по (a, b, c) и условии запроса WHERE a = 5 AND b >= 42 AND c < 77, индекс может быть просканирован от первой записи с a = 5 и b = 42 до последней записи с a = 5. Индексные записи с c >= 77 никогда не потребуется фильтровать на уровне таблицы, но пропускать их внутри индекса может быть выгодно или невыгодно. Когда происходит пропуск, сканирование начинает новый поиск по индексу, перемещаясь с конца текущей группы a = 5 и b = N (то есть с позиции в индексе, где появляется первый кортеж a = 5 AND b = N AND c >= 77), к началу следующей такой группы (то есть к позиции в индексе, где появляется первый кортеж a = 5 AND b = N + 1).
Многоколоночный индекс GiST может использоваться с условиями запроса, которые включают любой поднабор столбцов индекса. Условия на дополнительные столбцы ограничивают записи, возвращаемые индексом, но условие на первый столбец является наиболее важным чтобы определить, сколько индекса необходимо просканировать. Индекс GiST будет относительно неэффективным, если его первый столбец имеет только несколько различных значений, даже если в дополнительных столбцах есть много различных значений.
Многоколоночный индекс GIN может использоваться с условиями запроса, которые включают любое подмножество колонок индекса. В отличие от B-дерева или GiST, эффективность поиска по индексу одинакова независимо от того, какие колонки индекса используются в условиях запроса.
Многостолбцовый индекс BRIN может использоваться с условиями запроса, которые
включают любое подмножество столбцов индекса. Как GIN и в отличие от B-дерева или
GiST, эффективность поиска по индексу одинакова независимо от того, какие столбцы
индекса используются в условиях запроса. Единственная причина иметь несколько индексов BRIN
вместо одного многостолбцового индекса BRIN на одной таблице - это наличие
различного параметра хранения pages_per_range.
Конечно, каждая колонка должна использоваться с операторами, соответствующими типу индекса; предложения, которые включают другие операторы, не будут рассматриваться.
Следует использовать мультиколоночные индексы с осторожностью. В большинстве ситуаций индекс на одной колонке достаточен и экономит пространство и время. Индексы с более чем тремя колонками вряд ли будут полезны, если использование таблицы чрезвычайно стилизовано. См. также Раздел 11.5 и Раздел 11.9 для обсуждения достоинств различных конфигураций индексов.