BUG #11559: ANY array filters should make use of GIN indexes

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?

Responses

Browse pgsql-bugs by date

  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