单列索引
Postgres 可以在单个查询中非常有效地组合多个索引位图索引扫描。大多数时候,会选择最具选择性的索引(或两个,与位图索引扫描相结合),其余的将被过滤。一旦结果集足够窄,扫描更多索引就效率不高。
多列索引
完美匹配还是比较快的多列索引 https://www.postgresql.org/docs/current/indexes-multicolumn.html,但不是数量级。
既然你想包括一个数组类型我建议使用GIN指数。 AFAIK,数组类型上的通用 GiST 索引缺少运算符类。 (例外情况是intarray https://www.postgresql.org/docs/current/intarray.html for integer
数组。)
要包括integer
列,首先安装附加模块btree_gin https://www.postgresql.org/docs/current/btree-gin.html,它提供了必要的 GIN 运算符类。跑步每个数据库一次:
CREATE EXTENSION btree_gin;
然后您应该能够创建多列索引:
CREATE INDEX tbl_abc_gin_idx ON tbl USING GIN(a, b, c);
索引列的顺序与 GIN 索引无关。手册: https://www.postgresql.org/docs/current/indexes-multicolumn.html
多列 GIN 索引可与涉及以下的查询条件一起使用
索引列的任何子集。与 B 树或 GiST 不同,索引搜索
无论哪个索引列,有效性都是相同的
查询条件的使用。
最近邻搜索
由于您包含了 PostGisgeometry
类型,您可能想做一个最近邻搜索 https://postgis.net/workshops/postgis-intro/knn.html,为此你需要一个GiST指数。在这种情况下我建议two指标:
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
column c
到其中一个或两个。这取决于。
为此,您需要btree_gin https://www.postgresql.org/docs/current/btree-gin.html or btree_gist https://www.postgresql.org/docs/current/btree-gist.html或分别。