Thread: GIN index isn’t working with intarray

GIN index isn’t working with intarray

From

"Maeldron T."

Date:

22 December 2015, 01:18:25

Hello,

tested it with 9.3 and 9.5rc1 both.

psql (9.5rc1)
Type "help" for help.

test2=# create table test (ids int[]);
CREATE TABLE
test2=# insert into test (ids) values (array[11,22]);
INSERT 0 1
test2=# insert into test (ids) values (array[33,44,55]);
INSERT 0 1
test2=# insert into test (ids) values (array[66,77]);
INSERT 0 1
test2=# select * from test where ids && array[77];
   ids
---------
{66,77}
(1 row)

test2=# create index test_gin on test using gin (ids);
CREATE INDEX

test2=# set enable_seqscan = false;
SET
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)

test2=# drop extension intarray ;
DROP EXTENSION
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.007..0.007 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.005..0.005 rows=1 loops=1)
         Index Cond: (ids && '{77}'::integer[])
Planning time: 0.068 ms
Execution time: 0.019 ms
(7 rows)

test2=#

Please note that I used "set enable_seqscan = false" here because the table had only a few rows. I have tested this with real tables and the results are the same. As soon as intarray is created, the &&, @>, <@ operators stop using the GIN index.

The manual says they should work: http://www.postgresql.org/docs/9.3/static/intarray.html

Am I doing something wrong?

Thank you.

Re: GIN index isn’t working with intarray

From

Tom Lane

Date:

22 December 2015, 02:15:42

"Maeldron T." <maeldron@gmail.com> writes:
> As soon as intarray is created, the &&, @>, <@ operators
> stop using the GIN index.

intarray defines its own versions of those operators, which have nothing
to do with the standard GIN index.  You can create an index using
intarray's custom GIN opclass, instead.

            regards, tom lane

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

From

Jeff Janes

Date:

22 December 2015, 06:45:25

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

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

From

"Maeldron T."

Date:

22 December 2015, 23:50:08

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.

2015-12-22 4:45 GMT+01:00 Jeff Janes <jeff.janes@gmail.com>:

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