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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers