使用不同数据类型的三个字段创建多列索引

Question

我有一个带有3个字段的postgres表：

• a：postgis几何类型
• b：数组 varchar []
• c：整数

我有一个涉及所有这些字段的查询。我想添加一个多列索引来加快速度，但是由于它们的特性，这3个字段不能放在同一个索引下。

那么，在这种情况下应该采取什么策略呢？添加gist、gin和btree三个索引，然后postgresql将在查询期间使用它们吗？

- nourdine

---

你能展示一下相关的表格和查询语句，以及它的 explain analyze 吗？ - Craig Ringer

Answer 1

单列索引

Postgres 可以使用位图索引扫描非常高效地在单个查询中组合多个索引。大多数情况下，选择最具选择性的索引（或两个与位图索引扫描结合），其余部分被过滤。一旦结果集足够狭窄，扫描更多索引就不再有效。

多列索引

拥有完全匹配的多列索引仍然比较快，但并不是数量级的差异。
因为您想要包含一个数组类型，我建议使用GIN索引。据我所知，在数组类型上通用的GiST索引缺少操作符类。（例外是integer数组的intarray。）

为了包括 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);

你可以将整数列 c 添加到一个或两个索引中，具体情况取决于您的需求。为此，您需要使用 btree_gin 或 btree_gist 或两者都使用。

- Erwin Brandstetter

Answer 2

由于它们的特性，这3个字段不能放在同一索引下。

使用btree-gist模块，这3个字段可以放在同一索引下。

- Richard Huxton

---

哦，我明白了...但是我不知道如何创建多列索引:( 你能吗？它只显示：CREATE INDEX testidx ON test USING gist (a); 但那不是多列的，对吧？ - nourdine

如果您只是加载扩展并像通常一样使用列出的多个列创建它，它是否无法正常工作？ - Richard Huxton

哦，好的，所以我需要加载一个扩展程序才能做到那样。现在一切都清楚了。谢谢。 - nourdine