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

On 12.04.2017 17:24, Tom Lane wrote:
> TBH, I'm not sure you need to do any of that work. Have you got evidence
> that the planner will fail to choose the right plan regardless? I'm
> particularly unconvinced that choose_bitmap_and is a critical problem,
> because once you're into having to AND multiple indexes, you're talking
> about an expensive query anyhow.
The most expensive part would probably be accessing the heap in the
bitmap heap scan. It may be worth trying to choose an index path that
checks all the restriction and therefore allows us to skip this heap
access. This path might not be the cheapest one, though. The standard
AND selection procedure would have discarded it based on cost.
I've seen this happen on the regression database. Somehow I can't seem
to reproduce it on my earlier full-text search example.

An example:

regression=# explain select count(*) from tenk1 where hundred < 90 and
thousand < 31;
QUERY PLAN
-------------------------------------------------------------------------------------------
Bitmap Count on tenk1 (cost=182.69..185.56 rows=1 width=8)
Recheck Cond: ((thousand < 31) AND (hundred < 90))
-> BitmapAnd (cost=182.69..182.69 rows=287 width=0)
-> Bitmap Index Scan on tenk1_thous_tenthous (cost=0.00..6.68
rows=319 width=0)
Index Cond: (thousand < 31)
-> Bitmap Index Scan on tenk1_hundred (cost=0.00..175.62
rows=8978 width=0)
Index Cond: (hundred < 90)
(7 rows)

regression=# set enable_bitmapcount to off;
SET
regression=# explain select count(*) from tenk1 where hundred < 90 and
thousand < 31;
QUERY PLAN
-------------------------------------------------------------------------------------------
Aggregate (cost=375.34..375.35 rows=1 width=8)
-> Bitmap Heap Scan on tenk1 (cost=6.75..374.62 rows=287 width=0)
Recheck Cond: (thousand < 31)
Filter: (hundred < 90)
-> Bitmap Index Scan on tenk1_thous_tenthous (cost=0.00..6.68
rows=319 width=0)
Index Cond: (thousand < 31)
(6 rows)

--

Alexander Kuzmenkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company