Re: Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan - Mailing list pgsql-hackers

From Etsuro Fujita
Subject Re: Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan
Date
Msg-id 009301cef26a$525ec200$f71c4600$@etsuro@lab.ntt.co.jp
Whole thread Raw
In response to Re: Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan  ("Etsuro Fujita" <fujita.etsuro@lab.ntt.co.jp>)
List pgsql-hackers
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




pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: spinlocks storm bug
Next
From: Haribabu kommi
Date:
Subject: Re: Performance Improvement by reducing WAL for Update Operation