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?
推荐答案
是的,你可以索引一个数组,但你必须使用 数组运算符 和 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
笔记
似乎在许多情况下gin__int_ops 选项是必需的
create index <index_name> on <table_name> using GIN (<column> gin__int_ops)
我还没有看到它可以与 && 一起使用的情况.和 @> 操作符没有 gin__int_ops 选项
I have not yet seen a case where it would work with the && and @> operator without the gin__int_ops options
这篇关于PostgreSQL 可以索引数组列吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!