11.3. Составные индексы #
Индексы можно создавать и по нескольким столбцам таблицы. Например, если у вас есть таблица:
CREATE TABLE test2 ( major int, minor int, name varchar );
(предположим, что вы поместили в неё содержимое каталога /dev
) и вы часто выполняете запросы вида:
SELECT name FROM test2 WHERE major =константа
AND minor =константа
;
тогда имеет смысл определить индекс, покрывающий оба столбца major
и minor
. Например:
CREATE INDEX test2_mm_idx ON test2 (major, minor);
В настоящее время составными могут быть только индексы типов B-дерево, GiST, GIN и BRIN. Возможность построения индекса по нескольким ключевым столбцам не зависит от возможности добавления в индекс неключевых столбцов (INCLUDE
). Число столбцов в индексе ограничивается 32, включая столбцы INCLUDE
. (Этот предел можно изменить при компиляции Postgres Pro.)
Составной индекс-B-дерево может применяться в условиях с любым подмножеством столбцов индекса, но наиболее эффективен он при ограничениях по ведущим (левым) столбцам. Точное правило состоит в том, что сканируемая область индекса определяется условиями равенства с ведущими столбцами и условиями неравенства с первым столбцом, не участвующим в условии равенства. Ограничения столбцов правее них также проверяются по индексу, так что обращение к таблице откладывается, но на размер сканируемой области индекса это уже не влияет. Например, если есть индекс по столбцам (a, b, c)
и условие WHERE a = 5 AND b >= 42 AND c < 77
, индекс будет сканироваться от первой записи a
= 5 и b
= 42 до последней с a
= 5. Записи индекса, в которых c
>= 77, не будут учитываться, но тем не менее будут просканированы. Этот индекс в принципе может использоваться в запросах с ограничениями по b
и/или c
, без ограничений столбца a
, но при этом будет просканирован весь индекс, так что в большинстве случаев планировщик предпочтёт использованию индекса полное сканирование таблицы.
Составной индекс GiST может применяться в условиях с любым подмножеством столбцов индекса. Условия с дополнительными столбцами ограничивают записи, возвращаемые индексом, но в первую очередь сканируемая область индекса определяется ограничением первого столбца. GiST-индекс будет относительно малоэффективен, когда первый его столбец содержит только несколько различающихся значений, даже если дополнительные столбцы дают множество различных значений.
Составной индекс GIN может применяться в условиях с любым подмножеством столбцов индекса. В отличие от индексов GiST или B-деревьев, эффективность поиска по нему не меняется в зависимости от того, какие из его столбцов используются в условиях запроса.
Составной индекс BRIN может применяться в условиях запроса с любым подмножеством столбцов индекса. Подобно индексу GIN и в отличие от B-деревьев или GiST, эффективность поиска по нему не меняется в зависимости от того, какие из его столбцов используются в условиях запроса. Единственное, зачем в одной таблице могут потребоваться несколько индексов BRIN вместо одного составного индекса — это затем, чтобы применялись разные параметры хранения pages_per_range
.
При этом, разумеется, каждый столбец должен использоваться с операторами, соответствующими типу индекса; ограничения с другими операторами рассматриваться не будут.
Составные индексы следует использовать обдуманно. В большинстве случаев индекс по одному столбцу будет работать достаточно хорошо и сэкономит время и место. Индексы по более чем трём столбцам вряд ли будут полезными, если только таблица не используется крайне однообразно. Описание достоинств различных конфигураций индексов можно найти в Разделе 11.5 и Разделе 11.9.