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

On Mon, Dec 21, 2015 at 2:18 PM, Maeldron T. <maeldron(at)gmail(dot)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