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?  (Sergei Shelukhin <realgeek@gmail.com>)
Re: any way to get rid of Bitmap Heap Scan recheck?  (Heikki Linnakangas <heikki@enterprisedb.com>)
Re: any way to get rid of Bitmap Heap Scan recheck?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: "Shoaib Mir"
Дата:
Сообщение: Re: pg_stats how-to?
Следующее
От: Kenneth Marshall
Дата:
Сообщение: Re: performance drop on 8.2.4, reverting to 8.1.4