Thread: BUG #5183: Wrong select results using multicolumn gin indexes

BUG #5183: Wrong select results using multicolumn gin indexes

From
"Yury Don"
Date:
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)

Re: BUG #5183: Wrong select results using multicolumn gin indexes

From
Tom Lane
Date:
"Yury Don" <yura@vpcit.ru> writes:
> 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)

Ick.  I can reproduce this here using CVS HEAD, and I notice that
it starts to give the right answer after "VACUUM tmp".  I infer that
what is broken is the checks of tuples in the pending-insert list.
I don't have time to look closer right now --- Oleg, Teodor, can you
look at this?

(In the meantime, you can probably work around it by disabling
fastupdate on these indexes.)

            regards, tom lane

Re: BUG #5183: Wrong select results using multicolumn gin indexes

From
Teodor Sigaev
Date:
Will see

Tom Lane wrote:
> "Yury Don" <yura@vpcit.ru> writes:
>> 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)
>
> Ick.  I can reproduce this here using CVS HEAD, and I notice that
> it starts to give the right answer after "VACUUM tmp".  I infer that
> what is broken is the checks of tuples in the pending-insert list.
> I don't have time to look closer right now --- Oleg, Teodor, can you
> look at this?
>
> (In the meantime, you can probably work around it by disabling
> fastupdate on these indexes.)
>
>             regards, tom lane
>

--
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/

Re: BUG #5183: Wrong select results using multicolumn gin indexes

From
Teodor Sigaev
Date:
Thank you, fixed.

> 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.
--
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/