Re: [HACKERS] index-only count(*) for indexes supporting bitmap scans

Поиск
Список
Период
Сортировка
От Alexander Kumenkov
Тема Re: [HACKERS] index-only count(*) for indexes supporting bitmap scans
Дата
Msg-id 122a32e4-73bd-ec85-9dad-a72729635c65@postgrespro.ru
обсуждение исходный текст
Ответ на Re: [HACKERS] index-only count(*) for indexes supporting bitmap scans  (Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>)
Ответы Re: [HACKERS] index-only count(*) for indexes supporting bitmap scans  (Alexey Chernyshov <a.chernyshov@postgrespro.ru>)
Список pgsql-hackers
Дерево обсуждения
Hello everyone,

I made a new patch according to the previous comments. It is simpler now, only adding a few checks to the bitmap heap scan node. When the target list for the bitmap heap scan is empty, and there is no filter, and the bitmap page generated by the index scan is exact, and the corresponding heap page is visible to all transaction, we don't fetch it.

The performance is better than with the previous patch, because now it can leverage the parallel heap scan logic. A simple benchmark is attached: this patch is more than ten times faster on a frequent search term, and two times faster on an infrequent one.

Still, there is one thing that is bothering me. I use empty targetlist as the marker of that I should not fetch tuples. Because of that, I have to make sure that use_physical_tlist() doesn't touch empty tlists. Consequently, if count(*) sits on top of a subquery, this subquery has to project and cannot be deleted (see trivial_subqueryscan). There is such a query in the regression test select_distinct: "select count(*) from (select distinct two, four, two from tenk1);". For that particular query it shouldn't matter much, so I changed the test, but the broader implications of this escape me at the moment.

The cost estimation is very simplified now: I just halve the number of pages to be fetched. The most important missing part is checking whether we have any quals that are not checked by the index: if we do, we'll have to fetch all the tuples. Finding nonindex qualifications is somewhat convoluted for the bitmap index scan tree involving multiple indexes, so I didn't implement it for now. We could also consider estimating the number of lossy pages in the tid bitmap given current work_mem size.

I'll be glad to hear your thoughts on this.
Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Craig Ringer
Дата:
Сообщение: Re: [HACKERS] shm_mq_wait_internal gets stuck forever on fast shutdown
Следующее
От: Robert Haas
Дата:
Сообщение: Re: [HACKERS] shm_mq_wait_internal gets stuck forever on fast shutdown