在PostgreSQL中创建包含标量和数组列的多列索引 [英] Creating multicolumn index in PostgreSQL, containing both scalar and array columns

问题描述

为了优化复杂的PostgreSQL查询，我试图创建一个包含标量字符串和数组以及支持数组操作的索引（ @> ，< @ 和&& ）。

In order to optimize complex PostgreSQL query I've tried to create an index containing both scalar strings and arrays and supporting array operations (@>, <@ and &&).

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

But I only managed to create a BTREE index so far:

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

不支持数组操作（ @> ，< @ 和&& ）。

which have no support for array operations (@>, <@ and &&).

我尝试使用 GIN 和 GiST （使用 btree_gin 和 btree_gist extensions），但我发现无法在同一索引中同时使用标量和数组列。

I've tried to use GIN and GiST (using btree_gin and btree_gist extensions), but I found no way to use both scalar and array columns in the same index.

看起来像 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 数据类型的函数。但我不需要全文搜索。我甚至尝试创建自己的运算符类，但很快意识到它超出了我的范围。

The only way I found to create such an index is by using to_tsvector function to convert all scalar strings to tsvector datatype. But I don't need full text search here. I even tried to create my own operator class, but quickly realized that it's beyond me.

有没有办法创建多列 GIN / GiST index，包含标量字符串和数组？

Is there any way to create multicolumn GIN/GiST index, containing both scalar strings and arrays?

推荐答案

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

You need to install the additional module btree_gin or btree_gist respectively, which provide the missing operator classes.

每个数据库运行 ：

CREATE EXTENSION btree_gin;  -- or btree_gist

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

Then you should be able to create your multicolumn index:

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

详情：

• 具有异质性的3个字段的多列索引数据类型

• Multicolumn index on 3 fields with heterogenous data types

至于数组类型的索引： GIN 是那些完美的索引类型。 文档：

As for indexes on array types: GIN is the perfect index type for those. The documentation:

GIN索引是反向索引，它可以处理包含
多个键的值，例如数组。

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

Bold emphasis mine. The operators @>, <@ and && are defined for various data types. Some of them cooperate with GiST indexes as well. But with arrays as operands, it's always GIN indexes AFAIC.

我今天在dba.SE上写了一个相关的答案，查询列表相关运营商：

I wrote a related answer on dba.SE only today, with queries to list related operators:

• 使用PostgreSQL内置运算符< @包括扩展名intarray 后

• Use PostgreSQL builtin operator <@ after including extension intarray

数据类型字符很可能不是你想要的：

And the data type character is most probably not what you want:

• 使用数据类型text存储字符串的任何缺点？

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

这篇关于在PostgreSQL中创建包含标量和数组列的多列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！