Re: [BUGS] GIN index isn’t working with intarray

On Mon, Dec 21, 2015 at 2:18 PM, Maeldron T. <maeldron@gmail.com> wrote:

> test2=# explain analyze select * from test where ids && array[77];
>                                                    QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------
>  Bitmap Heap Scan on test  (cost=8.01..12.02 rows=1 width=30) (actual
> time=0.013..0.013 rows=1 loops=1)
>    Recheck Cond: (ids && '{77}'::integer[])
>    Heap Blocks: exact=1
>    ->  Bitmap Index Scan on test_gin  (cost=0.00..8.01 rows=1 width=0)
> (actual time=0.009..0.009 rows=1 loops=1)
>          Index Cond: (ids && '{77}'::integer[])
>  Planning time: 0.049 ms
>  Execution time: 0.036 ms
> (7 rows)
>
> test2=# create extension intarray;
> CREATE EXTENSION
> test2=# explain analyze select * from test where ids && array[77];
>                                                     QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------
>  Seq Scan on test  (cost=10000000000.00..10000000001.04 rows=1 width=30)
> (actual time=0.059..0.060 rows=1 loops=1)
>    Filter: (ids && '{77}'::integer[])
>    Rows Removed by Filter: 2
>  Planning time: 0.082 ms
>  Execution time: 0.067 ms
> (5 rows)

intarray creates operators which take precedence over the default operators.

If you want to keep using the existing index, you have to qualify the
operators with their schema:

explain analyze select * from test where ids OPERATOR(pg_catalog.&&) array[77];

If you want to use the new versions (which don't tolerate NULLS) you
have to create in index for them:

create index test_gin2 on test using gin (ids gin__int_ops);

If you usually want the default version and only sometimes the
intarray version, you could load intarray into some other schema which
is not in your search_path, and then fully qualify the operators with
their schema when you want those ones.

Cheers,

Jeff