Многоколоночные индексы в PostgreSQL
Индекс может быть определен более чем для одного столбца таблицы. Например, если у вас есть таблица такого вида:
CREATE TABLE devtable (
major int,
minor int,
name varchar
);
(скажем, вы храните свой каталог /dev в базе данных...) и часто выдаете такие запросы, как:
SELECT name FROM devtable WHERE major = constant AND minor = constant;
тогда может быть уместно определить индекс для major и minor столбцов вместе, например:
CREATE INDEX devtable_mm_idx ON devtable (major, minor);
В настоящее время только типы индексов B-tree, GiST, GIN и BRIN поддерживают индексы с несколькими ключевыми столбцами. Может ли быть несколько ключевых столбцов, не зависит от того, можно ли добавить столбцы INCLUDE в индекс. Индексы могут иметь до 32 столбцов, включая столбцы INCLUDE.
Многостолбцовый индекс B-tree можно использовать с условиями запроса, включающими любое подмножество столбцов индекса, но индекс наиболее эффективен, когда существуют ограничения на ведущие (крайние левые) столбцы. Точное правило заключается в том, что ограничения равенства для ведущих столбцов, а также любые ограничения неравенства для первого столбца, не имеющего ограничения равенства, будут использоваться для ограничения сканируемой части индекса. Ограничения для столбцов справа от этих столбцов проверяются в индексе, поэтому они экономят посещения самой таблицы, но не уменьшают часть индекса, которую необходимо просмотреть. Например, при заданном индексе (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 можно использовать с условиями запроса, включающими любое подмножество столбцов индекса. В отличие от B-дерева или GiST, эффективность поиска по индексу одинакова независимо от того, какие столбцы индекса используются в условиях запроса.
Многостолбцовый индекс BRIN можно использовать с условиями запроса, включающими любое подмножество столбцов индекса. Подобно GIN и в отличие от B-дерева или GiST, эффективность поиска по индексу одинакова независимо от того, какие столбцы индекса используются в условиях запроса. Единственная причина иметь несколько индексов BRIN вместо одного многостолбцового индекса BRIN для одной таблицы — иметь другой параметр хранения pages_per_range.
Конечно, каждый столбец должен использоваться с операторами, соответствующими типу индекса; пункты, в которых участвуют другие операторы, рассматриваться не будут.
Многоколоночные индексы следует использовать с осторожностью. В большинстве случаев достаточно индекса для одного столбца, что экономит место и время. Индексы с более чем тремя столбцами вряд ли будут полезны, если только использование таблицы не будет чрезмерно стилизовано.
Читайте также:
- Типы индексов в PostgreSQL
- SQL в PostgreSQL: внешние ключи (Foreign Keys)
- SQL в PostgreSQL: транзакции
Комментарии
Отправить комментарий