我在文件中找不到这个问题的确切答案。如果列是数组类型，是否将所有输入的值单独索引?
我用一个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");
索引是否帮助此查询?
当前回答
现在可以为单个数组元素建立索引。例如:
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()的性能。 操作符。真的没有解决办法吗?
@Frank接受的答案告诉您使用数组操作符，这对于Postgres 11仍然是正确的。手册:
．.． PostgreSQL的标准发行版包含一个GIN操作符 类，它支持使用这些对象进行索引查询 运营商: < @ @ > ＝ & &
这里是标准发行版中GIN索引的内置操作符类的完整列表。
在Postgres中，索引绑定到操作符(为某些类型实现)，而不仅仅是数据类型或函数或其他任何东西。这是Postgres最初的伯克利设计的遗产，现在很难改变。而且总体来说还不错。这里有一个关于pgsql-bugs的帖子，Tom Lane对此进行了评论。
一些PostGis函数(如ST_DWithin())似乎违反了这个原则，但事实并非如此。这些函数在内部被重写以使用各自的操作符。
The indexed expression must be to the left of the operator. For most operators (including all of the above) the query planner can achieve this by flipping operands if you place the indexed expression to the right - given that a COMMUTATOR has been defined. The ANY construct can be used in combination with various operators and is not an operator itself. When used as constant = ANY (array_expression) only indexes supporting the = operator on array elements would qualify and we would need a commutator for = ANY(). GIN indexes are out.
Postgres目前还没有聪明到可以从中派生一个GIN-indexable表达式。对于初学者来说，constant = ANY (array_expression)并不完全等同于array_expression @> ARRAY[constant]。如果数组操作符中包含任何NULL元素，则返回一个错误，而any构造可以处理任意一侧的NULL。对于数据类型不匹配有不同的结果。
相关的答案:
检查Postgres数组中是否存在值 用于在JSON数组中查找元素的索引 如何过滤PgArray列类型? IS能以某种方式与ANY或ALL结合吗?
旁白
在使用没有NULL值的整数数组(int4，而不是int2或int8)时(就像您的例子所暗示的那样)，考虑额外的模块intarray，它提供了专门的、更快的操作符和索引支持。看到的:
如何在PostgreSQL中为数组元素创建索引? 比较数组是否相等，忽略元素的顺序
至于你的问题中没有得到回答的UNIQUE约束:它是在整个数组值上使用btree索引实现的(就像你怀疑的那样)，根本无助于搜索元素。细节:
PostgreSQL如何强制UNIQUE约束/它使用什么类型的索引?
现在可以为单个数组元素建立索引。例如:
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。注意，您需要为每个数组索引构建一个单独的索引，在我的示例中，我只索引了第一个元素。
是的，你可以索引一个数组，但你必须使用数组操作符和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选项的情况下，它与&&和@>操作符一起工作的情况
