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. Число колонок в индексе ограничивается 32. (Этот предел можно изменить при компиляции PostgreSQL; см. файл pg_config_manual.h.)

Составной индекс-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-деревьев, эффективность поиска по нему не меняется в зависимости от того, какие из его колонок используются в условиях запроса.

При этом, разумеется, каждая колонка должна использоваться с операторами, соответствующими типу индекса; ограничения с другими операторами рассматриваться не будут.

Составные индексы следует использовать обдуманно. В большинстве случаев индекс по одной колонке будет работать достаточно хорошо и сэкономит время и место. Индексы по более чём трём колонкам вряд ли будут полезными, если только таблица не используется крайне однообразно. Описание достоинств различных конфигураций индексов можно найти в Разделе 11.5.