From: | rafal(at)conjur(dot)net |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #11559: ANY array filters should make use of GIN indexes |
Date: | 2014-10-03 15:16:26 |
Message-ID: | 20141003151626.1666.24873@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 11559
Logged by: Rafal Rzepecki
Email address: rafal(at)conjur(dot)net
PostgreSQL version: 9.3.5
Operating system: Linux
Description:
Consider schema:
create table arr_test(strs text[]);
insert into arr_test select array[i, i+1] from generate_series(1, 1000) i;
create index strs_idx on arr_test using gin(strs);
In this schema, query
select * from arr_test where '2' = any(strs);
yields a plan
QUERY PLAN
----------------------------------------------------------
Seq Scan on arr_test (cost=0.00..31.50 rows=2 width=37)
Filter: ('2'::text = ANY (strs))
yet (AFAICT) equivalent query
select * from arr_test where array['2'] && strs;
uses the index:
QUERY PLAN
-----------------------------------------------------------------------
Bitmap Heap Scan on arr_test (cost=8.02..13.21 rows=2 width=37)
Recheck Cond: ('{2}'::text[] && strs)
-> Bitmap Index Scan on strs_idx (cost=0.00..8.02 rows=2 width=0)
Index Cond: ('{2}'::text[] && strs)
In one of our databases with ~150k rows this makes for a difference in
speed of four orders of magnitude.
Is it possible to have the optimizer automatically use index for ANY queries
like that? Or are these queries inequivalent in some non-straightforward
manner?
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2014-10-05 07:31:26 | Re: BUG #11559: ANY array filters should make use of GIN indexes |
Previous Message | Gary Weaver | 2014-10-03 13:54:39 | Re: BUG #11554: Enforce that number and type of both columns involved in fkey are same type |