PostgreSQL索引数组列可以吗？

Question

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");

索引对此查询有帮助吗

Answer 1

是的，可以索引数组，但必须使用数组运算符而GIN索引类型.例子：&nbsp;&nbsp;&nbsp;&nbsp;CREATE&nbsp;TABLE&nbsp;"Test"("Column1"&nbsp;int[]); &nbsp;&nbsp;&nbsp;&nbsp;INSERT&nbsp;INTO&nbsp;"Test"&nbsp;VALUES&nbsp;('{10,&nbsp;15,&nbsp;20}'); &nbsp;&nbsp;&nbsp;&nbsp;INSERT&nbsp;INTO&nbsp;"Test"&nbsp;VALUES&nbsp;('{10,&nbsp;20,&nbsp;30}'); &nbsp;&nbsp;&nbsp;&nbsp;CREATE&nbsp;INDEX&nbsp;idx_test&nbsp;on&nbsp;"Test"&nbsp;USING&nbsp;GIN&nbsp;("Column1"); &nbsp;&nbsp;&nbsp;&nbsp;--&nbsp;To&nbsp;enforce&nbsp;index&nbsp;usage&nbsp;because&nbsp;we&nbsp;have&nbsp;only&nbsp;2&nbsp;records&nbsp;for&nbsp;this&nbsp;test...&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;SET&nbsp;enable_seqscan&nbsp;TO&nbsp;off; &nbsp;&nbsp;&nbsp;&nbsp;EXPLAIN&nbsp;ANALYZE&nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp;*&nbsp;FROM&nbsp;"Test"&nbsp;WHERE&nbsp;"Column1"&nbsp;@>&nbsp;ARRAY[20];结果：Bitmap&nbsp;Heap&nbsp;Scan&nbsp;on&nbsp;"Test"&nbsp;&nbsp;(cost=4.26..8.27&nbsp;rows=1&nbsp;width=32)&nbsp;(actual&nbsp;time=0.014..0.015&nbsp;rows=2&nbsp;loops=1) &nbsp;&nbsp;Recheck&nbsp;Cond:&nbsp;("Column1"&nbsp;@>&nbsp;'{20}'::integer[]) &nbsp;&nbsp;->&nbsp;&nbsp;Bitmap&nbsp;Index&nbsp;Scan&nbsp;on&nbsp;idx_test&nbsp;&nbsp;(cost=0.00..4.26&nbsp;rows=1&nbsp;width=0)&nbsp;(actual&nbsp;time=0.009..0.009&nbsp;rows=2&nbsp;loops=1) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Index&nbsp;Cond:&nbsp;("Column1"&nbsp;@>&nbsp;'{20}'::integer[])Total&nbsp;runtime:&nbsp;0.062&nbsp;ms注似乎在许多情况下杜松子酒需要选择create&nbsp;index&nbsp;<index_name>&nbsp;on&nbsp;<table_name>&nbsp;using&nbsp;GIN&nbsp;(<column>&nbsp;gin__int_ops)我还没有见过在不使用gin_INT_OPS选项的情况下与&and@>操作符一起工作的情况。

Answer 2

要用数组运算符，也就是仍然正确给Postgres 11号。手册：..PostgreSQL的标准发行版包括一个用于数组的GIN运算符类，它支持使用以下操作符进行索引查询：<@@>=&&标准发行版中GIN索引的内置操作符类的完整列表在这里。在Postgres索引绑定到运算符。(它是为某些类型实现的)，而不是单独的数据类型或函数或其他任何东西。那是从最初的伯克利设计中继承的Postgres现在很难改变。一般情况下都很好。下面是pgsql-bug上的一个线程，TomLane对此进行了评论。一些PostGis功能(比如ST_DWithin())似乎违反了这个原则，但事实并非如此。这些函数在内部重写以使用各自的操作者.索引表达式必须位于左边接线员。对于大多数运营商(包括上述所有)如果将索引表达式放置在右侧，则查询计划器可以通过翻转操作数来实现这一点。COMMUTATOR已经被定义了。这个ANY构造可以与各种操作符结合使用，而不是操作符本身。当用作constant = ANY (array_expression)只支持=操作员开启阵列元件我们需要一个交换器= ANY()..杜松子酒指数出来了。Postgres目前还不够聪明，无法从它派生出一个GIN-索引表达式。首先，constant = ANY (array_expression)是不完全等价到array_expression @> ARRAY[constant]..数组运算符返回错误(如果任何NULL)。元素参与其中，而ANY构造可以在任何一方处理NULL。对于数据类型的不匹配，有不同的结果。有关答复：检查Postgres数组中是否存在值在JSON数组中查找元素的索引SQLAlchemy：如何对PgArray列类型进行筛选？在某种程度上可以区别于与任何或全部相结合吗？旁白在工作中integer阵列&nbsp;(int4，不是int2或int8)没有NULL值(如示例所示)考虑附加模块intarray，这提供了专门的、更快的操作符和索引支持。见：如何在PostgreSQL中为数组元素创建索引？比较数组是否相等，忽略元素的顺序至于UNIQUE问题中未回答的约束：该约束是用btree索引实现的。全阵值(就像您所怀疑的那样)，并且无助于搜索元素完全没有。详情：PostgreSQL如何执行唯一约束/它使用什么类型的索引？

Answer 3

现在可以对单个数组元素进行索引。例如：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;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QUERY PLAN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;------------------------------------------------------------------------------------------------------------------&nbsp;Index Scan using test_index on test&nbsp; (cost=0.00..8.27 rows=1 width=32) (actual&nbsp; &nbsp;time=0.070..0.071 rows=1 loops=1)&nbsp; &nbsp;Index Cond: (foo[1] = 1)&nbsp;Total runtime: 0.112 ms(3 rows)这至少适用于Postgres 9.2.1。注意，您需要为每个数组索引构建一个单独的索引，在我的示例中，我只对第一个元素进行了索引。