BUG #5183: Wrong select results using multicolumn gin indexes - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5183
Logged by:          Yury Don
Email address:      yura@vpcit.ru
PostgreSQL version: 8.4.1
Operating system:   Debian Linux (architecture amd64)
Description:        Wrong select results using multicolumn gin indexes
Details:

We are using btree_gin module. I've created multicolumn gin indexes on table
with one column type tsvector and other column type integer. After index
creation selects works well. But after inserting new rows inserted row
appears in selects even when it does not satisfy conditions. The same
results with one column type int[] and other column type text or integer.
Below are examples:

mdb=# create table tmp (id integer not null primary key, t tsvector, i
integer);
CREATE TABLE
mdb=# insert into tmp values (1, 'word', 66);
INSERT 0 1
mdb=# create index tmp_idx on tmp using gin (t, i);
CREATE INDEX
mdb=# insert into tmp values (2, 'word', 86);
INSERT 0 1
mdb=# set enable_seqscan to off;
mdb=# select * from tmp where t @@ 'word' and i =66;
 id |   t    | i
----+--------+----
  1 | 'word' | 66
  2 | 'word' | 86
(2 rows)
mdb=# explain analyze select * from tmp where t @@ 'word' and i =66;
                                                   QUERY PLAN

----------------------------------------------------------------------------
------------------------------------
 Bitmap Heap Scan on tmp  (cost=3.01..6.01 rows=1 width=40) (actual
time=0.038..0.042 rows=2 loops=1)
   Recheck Cond: ((t @@ '''word'''::tsquery) AND (i = 66))
   ->  Bitmap Index Scan on tmp_idx  (cost=0.00..3.01 rows=1 width=0)
(actual time=0.027..0.027 rows=2 loops=1)
         Index Cond: ((t @@ '''word'''::tsquery) AND (i = 66))
 Total runtime: 0.097 ms
(5 rows)

drop table tmp;
DROP TABLE
create table tmp (id integer not null primary key, t text, i integer, a
int[]);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tmp_pkey"
for table "tmp"
CREATE TABLE
insert into tmp values (1, 'word', 66, '{1,2,3}');
INSERT 0 1
create index tmp_idx on tmp using gin (t, i, a gin__int_ops);
CREATE INDEX
insert into tmp values (2, 'word', 86, '{1,2,3}');
INSERT 0 1
set enable_seqscan to off;
SET
select * from tmp where t = 'word' and i=66 and a && '{1}';
 id |  t   | i  |    a
----+------+----+---------
  1 | word | 66 | {1,2,3}
  2 | word | 86 | {1,2,3}
(2 rows)

explain analyze select * from tmp where t = 'word' and i=66 and a && '{1}';
                                                   QUERY PLAN

----------------------------------------------------------------------------
------------------------------------
 Bitmap Heap Scan on tmp  (cost=4.26..8.28 rows=1 width=72) (actual
time=0.028..0.031 rows=2 loops=1)
   Recheck Cond: ((t = 'word'::text) AND (i = 66) AND (a &&
'{1}'::integer[]))
   ->  Bitmap Index Scan on tmp_idx  (cost=0.00..4.26 rows=1 width=0)
(actual time=0.014..0.014 rows=2 loops=1)
         Index Cond: ((t = 'word'::text) AND (i = 66) AND (a &&
'{1}'::integer[]))
 Total runtime: 0.096 ms
(5 rows)

drop table tmp;
DROP TABLE
create table tmp (id integer not null primary key, t text, a int[]);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tmp_pkey"
for table "tmp"
CREATE TABLE
insert into tmp values (1, 'word', '{1,2,3}');
INSERT 0 1
create index tmp_idx on tmp using gin (t, a gin__int_ops);
CREATE INDEX
insert into tmp values (2, 'word', '{4,5,6}');
INSERT 0 1
set enable_seqscan to off;
SET
select * from tmp where t = 'word' and a && '{1}';
 id |  t   |    a
----+------+---------
  1 | word | {1,2,3}
  2 | word | {4,5,6}
(2 rows)

explain analyze select * from tmp where t = 'word' and a && '{1}';
                                                   QUERY PLAN

----------------------------------------------------------------------------
------------------------------------
 Bitmap Heap Scan on tmp  (cost=4.26..8.28 rows=1 width=68) (actual
time=0.018..0.022 rows=2 loops=1)
   Recheck Cond: ((t = 'word'::text) AND (a && '{1}'::integer[]))
   ->  Bitmap Index Scan on tmp_idx  (cost=0.00..4.26 rows=1 width=0)
(actual time=0.014..0.014 rows=2 loops=1)
         Index Cond: ((t = 'word'::text) AND (a && '{1}'::integer[]))
 Total runtime: 0.054 ms
(5 rows)


drop table tmp;
DROP TABLE
create table tmp (id integer not null primary key, i integer, a int[]);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tmp_pkey"
for table "tmp"
CREATE TABLE
insert into tmp values (1, 66, '{1,2,3}');
INSERT 0 1
create index tmp_idx on tmp using gin (i, a gin__int_ops);
CREATE INDEX
insert into tmp values (2, 86, '{1,2,3}');
INSERT 0 1
set enable_seqscan to off;
SET
select * from tmp where i=66 and a && '{1}';
 id | i  |    a
----+----+---------
  1 | 66 | {1,2,3}
  2 | 86 | {1,2,3}
(2 rows)

explain analyze select * from tmp where i=66 and a && '{1}';
                                                   QUERY PLAN

----------------------------------------------------------------------------
------------------------------------
 Bitmap Heap Scan on tmp  (cost=4.26..8.28 rows=1 width=40) (actual
time=0.015..0.018 rows=2 loops=1)
   Recheck Cond: ((i = 66) AND (a && '{1}'::integer[]))
   ->  Bitmap Index Scan on tmp_idx  (cost=0.00..4.26 rows=1 width=0)
(actual time=0.010..0.010 rows=2 loops=1)
         Index Cond: ((i = 66) AND (a && '{1}'::integer[]))
 Total runtime: 0.049 ms
(5 rows)