GIN index isn’t working with intarray

From: "Maeldron T(dot)" <maeldron(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: GIN index isn’t working with intarray
Date: 2015-12-21 22:18:15
Message-ID: CAKatfS=FUO-SzM9h+bK121SHY434B1OsRFxBvsMHLjfWNxLfmA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

M.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2015-12-21 22:54:50 Re: BUG #9923: "reassign owned" does not change permissions grantor
Previous Message David G. Johnston 2015-12-21 16:09:30 Re: Fwd: Cannot log in as newly created user EXTRA INFO