From: Sergei Shelukhin <realgeek@gmail.com>
To: pgsql-performance@postgresql.org
Subject: any way to get rid of Bitmap Heap Scan recheck?
Date: 16 May 2007 22:42:27 -0700
Message-ID: <1179380547.753418.193030@w5g2000hsg.googlegroups.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: realgeek@gmail.com
Subject: Re: any way to get rid of Bitmap Heap Scan recheck?
In-Reply-To: <1179380547.753418.193030@w5g2000hsg.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox