The following bug has been logged online:
Bug reference: 5328
Logged by: Maxim Boguk
Email address: maxim.bo...@gmail.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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
