我有一个postgres数组列和一个gin索引:
CREATE TABLE things (
id integer NOT NULL,
tags character varying(255)[]
);
CREATE INDEX index_things_on_tags ON things USING gin (tags);
有几种方法可以使用各种数组运算符检查列中是否存在元素。以下是我所见过的:
select * from things where 'blue' = ANY (tags)
select * from things where tags <@ '{"blue"}'
select * from things where '{"blue","yellow"}' && tags;
在postgres 9.3中:
第一个会使用杜松子酒索引吗?
发布于 2014-03-01 06:13:29
为什么不测试一下呢?
regress=> SET enable_seqscan = off;
SET
regress=> explain select * from things where 'blue' = ANY (tags);
QUERY PLAN
---------------------------------------------------------------------------
Seq Scan on things (cost=10000000000.00..10000000037.67 rows=6 width=36)
Filter: ('blue'::text = ANY ((tags)::text[]))
(2 rows)
regress=> explain select * from things where tags <@ '{"blue"}';
QUERY PLAN
------------------------------------------------------------------------------------
Bitmap Heap Scan on things (cost=12.05..21.52 rows=6 width=36)
Recheck Cond: (tags <@ '{blue}'::character varying[])
-> Bitmap Index Scan on index_things_on_tags (cost=0.00..12.05 rows=6 width=0)
Index Cond: (tags <@ '{blue}'::character varying[])
(4 rows)
regress=> explain select * from things where '{"blue","yellow"}' && tags;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on things (cost=12.10..22.78 rows=12 width=36)
Recheck Cond: ('{blue,yellow}'::character varying[] && tags)
-> Bitmap Index Scan on index_things_on_tags (cost=0.00..12.09 rows=12 width=0)
Index Cond: ('{blue,yellow}'::character varying[] && tags)
(4 rows)
因此,Pg将索引用于&&
和<@
查询,而不是= ANY (...)
。
我确信教Pg将x = ANY (y)
转换成ARRAY[x] @> y
是可能的,但目前还不能。
2所做的就是你所说的你想要的。测试“蓝色”是否是其中一个标签。这不是一个平等测试,这是一个成员资格测试。
https://stackoverflow.com/questions/22106169
复制相似问题