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

On 10/03/2014 06:16 PM, rafal@conjur.net wrote:
> 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?

I think the queries are indeed equivalent. The planner just isn't smart
enough to do the transformation automatically.

(this is not a bug, just a missing feature)

- Heikki