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索引类型.

例子：

    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

注

似乎在许多情况下杜松子酒需要选择

create index <index_name> on <table_name> using GIN (<column> 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阵列 (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;

                                                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。注意，您需要为每个数组索引构建一个单独的索引，在我的示例中，我只对第一个元素进行了索引。