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

Question

我有一个包含 3 个字段的 postgres 表:

• a : postgis 几何
• b : 数组 varchar[]
• c : 整数

我有一个涉及所有这些的查询。我想添加一个多列索引来加快它的速度，但我不能，因为这 3 个字段因其性质而不能放在同一个索引下。

这种情况下的策略是什么？添加 3 个索引 gist、gin 和 btree，postgres 将在查询期间全部使用它们？

Answer 1

单列索引

Postgres 可以通过位图索引扫描在单个查询中非常有效地组合多个索引。大多数时候，选择最具选择性的索引(或两个，与位图索引扫描相结合)，其余的被过滤。一旦结果集足够窄，扫描更多索引就没有效率了。

多列索引

有一个完美匹配的multicolumn index还是比较快的, 但不是数量级。
由于您想包含一个数组类型，我建议使用一个GIN 索引。 AFAIK，数组类型上的通用 GiST 索引缺少运算符类。 (integer 数组的异常(exception)情况是 intarray。)

要包含 integer 列，首先安装附加模块 btree_gin ，它提供了必要的 GIN 运算符类。 每个数据库运行一次:

CREATE EXTENSION btree_gin;

然后您应该能够创建多列索引:

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

索引列的顺序与 GIN 索引无关。 The manual:

A multicolumn GIN index can be used with query conditions that involveany subset of the index's columns. Unlike B-tree or GiST, index searcheffectiveness is the same regardless of which index column(s) thequery conditions use.

最近邻搜索

由于您包含了 PostGis geometry 类型，因此您可能想要执行 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);

您可以将 integer 列 c 添加到其中一个或两个。这取决于。为此，您需要 btree_gin或 btree_gist或两者兼而有之。