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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
