arrays - 在 PostgreSQL 中创建包含标量列和数组列的多列索引

Question

为了优化复杂的 PostgreSQL 查询，我尝试创建一个包含标量字符串和数组并支持数组操作(@>、<@ 和 &&)的索引。

但我只设法创建了一个 BTREE到目前为止的索引:

CREATE INDEX idx1
  ON "MyTable"
  USING btree
  ("Char_1", "Array_1", "Array_2", "Array_3", "Char_2");

不支持数组操作(@>、<@ 和 &&)。

我试过使用 GIN和 GiST (使用 btree_gin 和 btree_gist 扩展名)，但我发现无法在同一索引中同时使用标量和数组列。

看起来像GIN不支持标量:

ERROR:  data type character has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

同时 GiST不支持数组:

ERROR:  data type character varying[] has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

我发现创建这样一个索引的唯一方法是使用 to_tsvector将所有标量字符串转换为 tsvector 的函数数据类型。但是我这里不需要全文搜索。我什至尝试创建自己的运算符类，但很快意识到这超出了我的能力范围。

有什么方法可以创建多列 GIN/GiST索引，同时包含标量字符串和数组？

Answer 1

您需要安装附加模块 btree_gin 或 btree_gist 分别提供缺少的运算符类。

每个数据库运行一次:

CREATE EXTENSION btree_gin;  -- or btree_gist

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

CREATE INDEX idx1 ON "MyTable" USING gin
  ("Varchar_1", "Array_1", "Array_2", "Array_3", "Varchar_2");

参见:

• Multicolumn index on 3 fields with heterogenous data types

对于array 类型的索引:GIN 是最适合这些类型的索引类型。 The manual:

GIN indexes are inverted indexes which can handle values that containmore than one key, arrays for example.

大胆强调我的。运营商@> , <@和 &&为各种数据类型定义。其中一些也与 GiST 索引合作。但是以数组作为操作数，它通常是 GIN 索引。见:

• Use PostgreSQL builtin operator <@ after including extension intarray

数据类型character很可能不是您想要的。见:

• Any downsides of using data type “text” for storing strings?