I wrote:
> Amit Khandekar wrote:
> > Yes, I agree that rather than looking at the bitmap heap scan to track
> > the number of pages, we should look somewhere in the underlying index
> > scan. Yes, we should get a constant number of index pages regardless
> > of the actual parent table rows.
> I agree with you. I'll modify the patch to show 1) the number of the
> exact/lossy pages in a TIDBitmap by examining the underlying index scan,
> not the number of these pages that have been fetched in the bitmap heap
> scan, and 2) the memory requirement.
Though at first I agreed on this, while working on this I start to think information about (2) is enough for tuning
work_mem. Here are examples using a version under development, where "Bitmap Memory Usage" means (peak) memory space
usedby a TIDBitmap, and "Desired" means the memory required to guarantee non-lossy storage of a TID set, which is shown
onlywhen the TIDBitmap has been lossified. (work_mem = 1MB.)
postgres=# EXPLAIN ANALYZE SELECT * FROM demo WHERE col2 between 0.0001 and 0.0005 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------Bitmap
HeapScan on demo (cost=77.14..12142.69 rows=3581 width=42) (actual time=1.748..53.203 rows=4112 loops=1) Recheck
Cond:((col2 >= 0.0001::double precision) AND (col2 <= 0.0005::double precision)) Bitmap Memory Usage: 315kB ->
BitmapIndex Scan on demo_col2_idx (cost=0.00..76.25 rows=3581 width=0) (actual time=1.113..1.113 rows=4112 loops=1)
Index Cond: ((col2 >= 0.0001::double precision) AND (col2 <= 0.0005::double precision))Total runtime: 53.804 ms
(6 rows)
postgres=# EXPLAIN ANALYZE SELECT * FROM demo WHERE col2 between 0.01 and 0.05 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------Bitmap
HeapScan on demo (cost=8307.41..107635.14 rows=391315 width=42) (actual time=84.818..2709.015 rows=400172 loops=1)
RecheckCond: ((col2 >= 0.01::double precision) AND (col2 <= 0.05::double precision)) Rows Removed by Index Recheck:
8815752 Bitmap Memory Usage: 1025kB (desired 20573kB) -> Bitmap Index Scan on demo_col2_idx (cost=0.00..8209.58
rows=391315width=0) (actual time=83.664..83.664 rows=400172 loops=1) Index Cond: ((col2 >= 0.01::double
precision)AND (col2 <= 0.05::double precision))Total runtime: 2747.088 ms
(7 rows)
We should look at (1) as well? (Honestly, I don't know what to show about (1) when using a bitmap scan on the inside
ofa nestloop join. For memory usage and desired memory I think the maximum values would be fine.) I re-wish to know
youropinion.
Thanks,
Best regards,
Etsuro Fujita
