any way to get rid of Bitmap Heap Scan recheck?
От | Sergei Shelukhin |
---|---|
Тема | any way to get rid of Bitmap Heap Scan recheck? |
Дата | |
Msg-id | 1179380547.753418.193030@w5g2000hsg.googlegroups.com обсуждение исходный текст |
Ответы |
Re: any way to get rid of Bitmap Heap Scan recheck?
Re: any way to get rid of Bitmap Heap Scan recheck? Re: any way to get rid of Bitmap Heap Scan recheck? |
Список | 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.
В списке pgsql-performance по дате отправления: