On Tue, Apr 11, 2017 at 8:24 PM, Alexander Kuzmenkov < [email protected]> wrote:
> I would like to propose a patch that speeds up the queries of the form > 'select > count(*) ... where ...', where the restriction clauses can be satisfied > by some > indexes. At the moment, such queries use index-only scans followed by > aggregation. Index-only scans are only possible for indexes that are > capable of > returning indexed tuples, that is, support the 'amgettuple' access method. > They > are not applicable to indexes such as GIN and RUM. However, it is possible > to > improve count(*) performance for indexes that support bitmap scans. Having > performed a bitmap index scan or a combination of such, the bits in bitmap > can > be counted to obtain the final result. Of course, the bitmap pages that are > lossy or not visible to all existing transactions will still require heap > access. > That's a cool feature for FTS users! Please, register this patch to the next commitfest. This patch has some important limitations: > * It only supports targetlist consisting of a single expression that can be > projected from count(*). > * count(expr) is not supported. We could support it for cases where the > "expr is not null" restriction can be satisfied with an index. > * The current implementation does not support parallel execution. It could > be > implemented during the PostgreSQL 11 release cycle. > * For some indexes, the bitmap index scan will always require rechecking > all > the tuples. Bitmap count plans should not be used in such cases. For now, > this > check is not implemented. > Does this limitation cause a performance drawback? When bitmap index scan returns all rechecks, alternative to Bitmap Count is still Aggregate + Bitmap Heap Scan. Thus, I think Bitmap Count would have the same performance or even slightly faster. That's worth testing. Also, what is planning overhead of this patch? That's worth testing too. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
