postgresql – 具有异构数据类型的3个字段的多列索引

首先,Postgres可以在单个查询中使用位图索引扫描非常有效地组合多个索引.大多数情况下,Postgres将选择最具选择性的索引(或两个并将它们与位图索引扫描结合起来)并在位图堆扫描后过滤其余的索引.一旦结果集足够窄,扫描另一个索引就没有效率.

多列索引

拥有完美匹配的multicolumn index仍然更快,但不是数量级.
由于你想要包含一个数组类型,我建议使用GIN索引.对于数组类型的通用GiST索引,缺少AFAIK运算符类. (整数数组的例外是intarray.)

要包含整数列,首先安装附加模块btree_gin,它提供必要的GIN运算符类.每个数据库运行一次：

CREATE EXTENSION btree_gin;

然后你应该能够创建你的多列索引：

CREATE INDEX tbl_abc_gin_idx ON tbl USING GIN(a,b,c);

索引列的顺序与GIN索引无关. Per documentation:

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几何类型,因此您可能想要执行nearest neighbour search,您需要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);

您可以将整数列c添加到其中一个或两个.这取决于.
为此,您需要分别为btree_gin或btree_gist或两者.