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

Thank you, Jeff. I considered removing the intarray extension as its
inclusion operators seem to be slower than the array inclusion operators
(on seq scans). The only thing I need from intarray is the idx.

However, its idx seem to be 2.5 times faster (on my data) than this:
https://wiki.postgresql.org/wiki/Array_Index. I use it for ordering so it
matters.

I will go with your suggestion so I can have the fast operators for
querying and the fast idx for ordering. It’s perfect.

I wish you all nice holidays.

M.

2015-12-22 4:45 GMT+01:00 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:

> 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
>