BUG #5328: GIN index with fastupdates=on provide wrong result on bitmap scan

From: "Maxim Boguk" <maxim(dot)boguk(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5328: GIN index with fastupdates=on provide wrong result on bitmap scan
Date: 2010-02-16 11:10:45
Message-ID: 201002161110.o1GBAj0u000846@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 5328
Logged by: Maxim Boguk
Email address: maxim(dot)boguk(at)gmail(dot)com
PostgreSQL version: 8.4.1
Operating system: linux kernel v 2.6.18
Description: GIN index with fastupdates=on provide wrong result on
bitmap scan
Details:

I cannot create self-containing test case because fresh loaded table from
dump don't have such effect.
Bug itself is returning some extra unrelated tuples from bitmap scan over
complex GIN index.
Bug not random server issue (same problem exists on the master database and
on both londiste slaves).

Here is query which show bug:
SELECT file_type_did FROM
"file"
WHERE
"file".file_type_did='69000003057'::bigint
AND "file".obj_status_did = 1
AND ("file".obj_tsvector @@ (make_tsquery('(море)',
'utf8_russian')))
offset 0
) AS t
where t.file_type_did<>'69000003057'::bigint;
---------------
69000006314
69000002260
69000006314
69000006314
69000002260
69000002260
...
69000002260
69000006314
(83 rows)

E.g. inner query returned 83 rows with complete unrelated file_type_did

Now let me show explain of an inner query:
db=# EXPLAIN SELECT file_type_did FROM
db-# "file"
db-# WHERE
db-# "file".file_type_did='69000003057'::bigint
db-# AND "file".obj_status_did = 1
db-# AND ("file".obj_tsvector @@ (make_tsquery('(море)',
'utf8_russian')))
db-# offset 0;
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Limit (cost=58.47..722.08 rows=5253 width=8)
-> Bitmap Heap Scan on file (cost=58.47..722.08 rows=5253 width=8)
Recheck Cond: ((file_type_did = 69000003057::bigint) AND
(obj_tsvector @@ '''море'''::tsquery) AND (obj_status_did = 1))
-> Bitmap Index Scan on i_file__photo_search (cost=0.00..57.16
rows=5253 width=0)
Index Cond: ((file_type_did = 69000003057::bigint) AND
(obj_tsvector @@ '''море'''::tsquery))

Description of used GIN index:
"i_file__photo_search" gin (file_type_did, photo_vislvl, obj_tsvector) WHERE
obj_status_did = 1

where is related table fields is:
file_type_did | bigint | not null
photo_vislvl | smallint | not null default 9
obj_tsvector | tsvector | not null default
''::tsvector

Additional information:
1)doing reindex index i_file__photo_search; remove an problem for a short
time (1-60 minutes depend user=update activity on the table).
2)VACUUM files; have the same effect for the same time.
3)8.3 server don't have such issues

My idea is bug related to GIN fastupdate engine which was implemented in 8.4
version.

I will do ALTER INDEX i_file__photo_search set (fastupdate=off); and VACUUM
file; on one of slaves and check results in next 24 hours to confirm or
contradict my idea.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Olivier Jeannet 2010-02-16 15:06:35 BUG #5329: postmaster fails to start because of an erroneous pg_hba.conf, but doesn't output any error
Previous Message Takahiro Itagaki 2010-02-16 06:51:55 Re: BUG #5326: The 2nd update of a table which has foreign keys is blocked.