可以PostgreSQL的数组索引列？ [英] Can PostgreSQL index array columns?

问题描述

我无法找到一个明确的答案，在文档中将这个问题。如果列是一个数组类型，将所有输入的数值分别索引？

I can't find a definite answer to this question in the documentation. If a column is an array type, will all the entered values be individually indexed?

我创建一个 INT [] 列一个简单的表格，并把唯一索引就可以了。我注意到，我不能添加整数的同一阵列，这使我相信该指数是数组中元素的组合，而不是每个项目的索引。

I created a simple table with one int[] column, and put a unique index on it. I noticed that I couldn't add the same array of ints, which leads me to believe the index is a composite of the array items, not an index of each item.

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

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

是索引帮助这个查询？

Is the index helping this query?

推荐答案

是的，你可以索引一个数组，但你必须使用的array运营商和 GIN指数型

Yes you can index an array, but you have to use the array operators and the GIN-index type.

例如：

    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

这篇关于可以PostgreSQL的数组索引列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！