Многоколонный индекс на 3 поля с разнородными типами данных
У меня есть таблица postgres с 3 полями:
- а: постгис геометрия
- b: массив varchar[]
- с: целое число
и у меня есть запрос, который включает в себя все из них. Я хотел бы добавить многоколонный индекс, чтобы ускорить его, но я не могу, так как 3 поля не могут идти под одним и тем же индексом из-за их природы.
Какова стратегия в этом случае? Добавление 3 индексов gist, gin и btree и postgres будут использовать их все во время запроса?
2 ответа
Индекс по одной колонке
Прежде всего, Postgres может очень эффективно объединять несколько индексов в одном запросе с помощью сканирования растровых индексов. В большинстве случаев Postgres выбирает наиболее селективный индекс (или два и объединяет их с сканированием растровых индексов) и фильтрует остальное после сканирования кучи растровых изображений. Если результирующий набор достаточно узок, сканировать другой индекс неэффективно.
Многоколонный индекс
Все еще быстрее иметь идеально соответствующий многоколоночный индекс, но не на порядки.
Поскольку вы хотите включить тип массива, я предлагаю использовать индекс GIN. AFAIK, классы операторов отсутствуют для индексов GiST общего назначения по типу массива. (Исключение intarray за integer массивы.)
Чтобы включить integer колонка, сначала установите дополнительный модуль btree_gin, который обеспечивает необходимые классы операторов GIN. Запустить один раз для базы данных:
CREATE EXTENSION btree_gin;
Тогда вы сможете создать свой многоколонный индекс:
CREATE INDEX tbl_abc_gin_idx ON tbl USING GIN(a, b, c);
Порядок столбцов индекса не имеет значения для индексов GIN. По документации:
Многоколонный индекс GIN может использоваться с условиями запроса, которые включают любое подмножество столбцов индекса. В отличие от B-дерева или GiST, эффективность поиска по индексу одинакова независимо от того, какие столбцы индекса используются условиями запроса.
Поиск ближайшего соседа
Поскольку вы включаете PostGis geometry Тип, скорее всего, вы хотите сделать поиск ближайшего соседа, для которого вам нужен индекс GiST. В этом случае я предлагаю два индекса:
CREATE INDEX tbl_ac_gist_idx ON tbl USING GiST(a, c); -- geometry type
CREATE INDEX tbl_bc_gin_idx ON tbl USING GIN(b, c);
Вы можете добавить integer колонка c или одному или обоим. Это зависит. Для этого вам нужно либо btree_gin или же btree_gist или оба, соответственно.
3 поля не могут идти под одним и тем же индексом из-за их природы