Thread
-
BUG #5183: Wrong select results using multicolumn gin indexes
Yury Don <yura@vpcit.ru> — 2009-11-12T18:14:02Z
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
Tom Lane <tgl@sss.pgh.pa.us> — 2009-11-12T21:44:25Z
"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
Teodor Sigaev <teodor@sigaev.ru> — 2009-11-13T09:56:50Z
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
Teodor Sigaev <teodor@sigaev.ru> — 2009-11-13T11:18:32Z
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/