PostgreSQL可以索引数组列吗?

我在文件中找不到这个问题的确切答案。如果列是数组类型，是否将所有输入的值单独索引?

我创建了一个简单的表，其中有一个int[]列，并在上面放置了一个唯一的索引。我注意到我不能添加相同的int数组，这使我相信索引是数组项的组合，而不是每个项的索引。

INSERT INTO "Test"."Test" VALUES ('{10, 15, 20}');
INSERT INTO "Test"."Test" VALUES ('{10, 20, 30}');


SELECT * FROM "Test"."Test" WHERE 20 = ANY ("Column1");

索引是否帮助此查询?

126307 次浏览
小开
最佳答案

是的，你可以索引一个数组，但你必须使用数组运算符和GIN-index类型。

例子:

    CREATE TABLE "Test"("Column1" int[]);
INSERT INTO "Test" VALUES ('{10, 15, 20}');
INSERT INTO "Test" VALUES ('{10, 20, 30}');


CREATE INDEX idx_test on "Test" USING GIN ("Column1");


-- To enforce index usage because we have only 2 records for this test...
SET enable_seqscan TO off;


EXPLAIN ANALYZE
SELECT * FROM "Test" WHERE "Column1" @> ARRAY[20];

结果:

Bitmap Heap Scan on "Test"  (cost=4.26..8.27 rows=1 width=32) (actual time=0.014..0.015 rows=2 loops=1)
Recheck Cond: ("Column1" @> '{20}'::integer[])
->  Bitmap Index Scan on idx_test  (cost=0.00..4.26 rows=1 width=0) (actual time=0.009..0.009 rows=2 loops=1)
Index Cond: ("Column1" @> '{20}'::integer[])
Total runtime: 0.062 ms
请注意

在许多情况下，gin__int_ops选项是必需的

create index <index_name> on <table_name> using GIN (<column> gin__int_ops)

我还没有看到一个案例，它将与&&和不带gin__int_ops选项的@>操作符

小开

现在可以为单个数组元素建立索引。例如:

CREATE TABLE test (foo int[]);
INSERT INTO test VALUES ('{1,2,3}');
INSERT INTO test VALUES ('{4,5,6}');
CREATE INDEX test_index on test ((foo[1]));
SET enable_seqscan TO off;


EXPLAIN ANALYZE SELECT * from test WHERE foo[1]=1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Index Scan using test_index on test  (cost=0.00..8.27 rows=1 width=32) (actual   time=0.070..0.071 rows=1 loops=1)
Index Cond: (foo[1] = 1)
Total runtime: 0.112 ms
(3 rows)

这至少适用于Postgres 9.2.1。注意，您需要为每个数组索引构建一个单独的索引，在我的示例中，我只索引了第一个元素。

小开

@Tregoreg在他提供的赏金的评论中提出了< /强> < >强问题:

我没有发现当前的答案工作。使用GIN指数 数组类型的列不会提高ANY()的性能。 操作符。真的没有解决办法吗?< / p >

@Frank's accepted answer告诉你使用数组运算符，这是Postgres 11的还是正确的。手册:

< p >…PostgreSQL的标准发行版包含一个GIN操作符 类，它支持使用这些对象进行索引查询 操作符:< / p > 
<@
@>
=
&&

标准分布中GIN索引的内置操作符类的完整列表在这里

在Postgres 索引绑定到操作符中(为某些类型实现)，不只是数据类型或函数或其他任何东西。这是继承了Postgres最初的伯克利设计，现在很难改变。而且总体来说还不错。这里有一个关于pgsql-bugs的帖子，Tom Lane对此进行了评论。

一些PostGis 函数(如ST_DWithin())似乎违反了这一原则，但事实并非如此。这些函数在内部被重写，使用各自的运算符。

索引表达式必须指向运算符的左。对于大多数操作符(包括以上所有)，如果将索引表达式放在右边，查询规划器可以通过翻转操作数来实现这一点——假设已经定义了COMMUTATOR。ANY构造可以与各种操作符组合使用，它本身不是一个操作符。当用作constant = ANY (array_expression)时，只有支持数组元素上的=操作符的索引才符合条件，并且我们需要一个= ANY()的换易子。GIN索引出来了。

Postgres目前还没有聪明到可以从中派生一个GIN-indexable表达式。对于初学者来说，constant = ANY (array_expression)是从不完全等价到array_expression @> ARRAY[constant]。数组操作符如果涉及任何NULL 元素将返回一个错误，而ANY构造可以处理任意一方的NULL。对于数据类型不匹配有不同的结果。

相关的答案:

旁白

当使用没有NULL值的integer数组 (int4，而不是int2或int8)时(就像你的例子所暗示的那样)，考虑额外的模块< >强intarray < / >强，它提供了专门的、更快的操作符和索引支持。看到的:

至于你的问题中没有回答的UNIQUE约束:它是通过整个数组值上的btree索引实现的(就像你怀疑的那样)，根本无助于搜索元素。细节:


提交答案