From: Sergei Shelukhin <realgeek@gmail.com>
Subject: any way to get rid of Bitmap Heap Scan recheck?
Date: 16 May 2007 22:42:27 -0700
Organization: http://groups.google.com
Lines: 32
Message-ID: <1179380547.753418.193030@w5g2000hsg.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
User-Agent: G2/1.0
Complaints-To: groups-abuse@google.com
Injection-Info: w5g2000hsg.googlegroups.com; posting-host=81.88.114.217;
	posting-account=r4W4SQ0AAADrEtDZob53QrVyaZ6liodo
To: pgsql-performance@postgresql.org

Hi.
I have the following join condition in a query
"posttag inner join tag ON posttag.tagid = tag.id and tag.name =
'blah'"
tag.id is PK, I have indexes on posttag.tagid and tag.name both
created  with all the options set to default.
PG version is 8.1.


The query is very slow (3 minutes on test data), here's what takes all
the time, from explain results:

>  Bitmap Heap Scan on tag  (cost=897.06..345730.89 rows=115159 width=8)
                           Recheck Cond: ((name)::text = 'blah'::text)
                           ->  Bitmap Index Scan on tag_idxn
(cost=0.00..897.06 rows=115159 width=0)
                                 Index Cond: ((name)::text =
'blah'::text)

What is recheck? I googled some and found something about lossy
indexes but no fixes for this issue.
The only reason I ever have this index is to do joins like this one;
how do I make it not lossy?

If I cannot make it not lossy, is there any way to make it skip
recheck and say to hell with the losses? :)
The query without recheck will run like up to 100 times faster
according to overall query plan.

I'm pondering encoding the tag name to int or bytea field(s) and
joining on them but that's kinda ugly.