Re: Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan

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 used by a TIDBitmap, and "Desired" means the memory required to guarantee non-lossy storage of a TID set, which is shown only when 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 Heap Scan 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
-> Bitmap Index 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 Heap Scan on demo (cost=8307.41..107635.14 rows=391315 width=42) (actual time=84.818..2709.015 rows=400172 loops=1)
Recheck Cond: ((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=391315 width=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 of a nestloop join. For memory usage and desired memory I think the maximum values would be fine.) I re-wish to know your opinion.

Thanks,

Best regards,
Etsuro Fujita