any way to get rid of Bitmap Heap Scan recheck?

From: Sergei Shelukhin <realgeek(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: any way to get rid of Bitmap Heap Scan recheck?
Date: 2007-05-17 05:42:27
Message-ID: 1179380547.753418.193030@w5g2000hsg.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Andrew Sullivan 2007-05-17 13:55:30 Re: Background vacuum
Previous Message Mark Kirkwood 2007-05-16 23:28:30 Re: Disk Fills Up and fsck "Compresses" it