具有异构数据类型的3个字段的多列索引 [英] Multicolumn index on 3 fields with heterogenous data types
问题描述
我有一个包含3个字段的postgres表：
I have a postgres table with 3 fields:
- a：postgis geometry
- b ：array varchar []
- c：integer
我有一个涉及所有的查询他们我想添加一个多列索引来加快速度，但由于它们的性质，我不能因为3个字段不能归入相同的索引。
and I have a query that involves all of them. I would like to add a multicolumn index to speed it up but I cannot as the 3 fields cannot go under the same index because of their nature.
什么是策略这个案例？添加3个索引gist，gin和btree以及postgres将在查询期间全部使用它们吗？
What is the strategy in this case? Adding 3 indexes gist, gin and btree and postgres will use them all during the query?
推荐答案
单列索引
首先，Postgres可以使用位图索引扫描在单个查询中非常有效地组合多个索引。大多数情况下，Postgres将选择最具选择性的索引（或两个并将它们与位图索引扫描结合起来）并在位图堆扫描后过滤其余的索引。一旦结果集足够窄，扫描另一个索引效率就不高。
Single-column index
First of all, Postgres can combine multiple indexes very efficiently in a single query with bitmap index scans. Most of the time, Postgres will pick the most selective index (or two and combine them with bitmap index scans) and filter the rest after a bitmap heap scan. Once the result set is narrow enough, it's not efficient to scan another index.
它是仍然可以更快地获得完全匹配的多列索引，但不是数量级。
由于您想要包含数组类型，我建议使用 GIN 索引。对于数组类型的通用GiST索引，缺少AFAIK运算符类。 （例外情况是 intarray for integer arrays。）
It is still faster to have a perfectly matching multicolumn index, but not by orders of magnitude.
Since you want to include an array type I suggest to use a GIN index. AFAIK, operator classes are missing for general-purpose GiST indexes on array type. (The exception being intarray for integer arrays.)
包含整数列，首先安装附加模块 btree_gin ，提供必要的GIN运算符类。每个数据库运行 ：
To include the integer column, first install the additional module btree_gin, which provides the necessary GIN operator classes. Run once per database:
CREATE EXTENSION btree_gin;
然后你应该可以创建多列索引：
Then you should be able to create your multicolumn index:
CREATE INDEX tbl_abc_gin_idx ON tbl USING GIN(a, b, c);
索引列的顺序与GIN索引无关。 每份文件：
The order of index columns is irrelevant for GIN indexes. Per documentation:
多列GIN索引可用于涉及
索引列的任何子集的查询条件。与B-tree或GiST不同，无论
查询条件使用哪个索引列，索引搜索
的有效性都是相同的。
A multicolumn GIN index can be used with query conditions that involve any subset of the index's columns. Unlike B-tree or GiST, index search effectiveness is the same regardless of which index column(s) the query conditions use.
最近邻搜索
由于你包含一个PostGis 几何类型，你很可能想要最近邻搜索，为此你需要一个 GiST 索引。在这种情况下，我建议两个索引：
Nearest neighbour search
Since you are including a PostGis geometry type, chances are you want to do a nearest neighbour search, for which you need a GiST index. In this case I suggest two indexes:
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);
您可以添加整数列 c 到一个或两个。这取决于。
为此，您需要 btree_gin 或 btree_gist 或两者兼而有之。
You could add the integer column c to either one or both. It depends.
For that, you need either btree_gin or btree_gist or both, respectively.
这篇关于具有异构数据类型的3个字段的多列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
