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

From: Amit Khandekar [mailto:amit.khandekar@enterprisedb.com]
> On 1 November 2013 16:32, Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote:
>> From: Fujii Masao [mailto:masao.fujii@gmail.com]

>>> I'm not sure if it's good idea to show the number of the fetches because it
>>> seems difficult to tune work_mem from that number. How can we calculate how
>>> much to increase work_mem to avoid lossy bitmap from the number of the fetches
>>> in EXPLAIN output?

>> We can calculate that from the following equation in tbm_create():

>>   nbuckets = maxbytes /
>>     (MAXALIGN(sizeof(HASHELEMENT)) + MAXALIGN(sizeof(PagetableEntry))
>>     + sizeof(Pointer) + sizeof(Pointer)),

>> where maxbytes is the size of memory used for the hashtable in a TIDBitmap,
>> designated by work_mem, and nbuckets is the estimated number of hashtable
>> entries we can have within maxbytes.  From this, the size of work_mem within
>> which we can have every hashtable entry as an exact bitmap is calculated as
>> follows:

>>   work_mem = (the number of exact pages + the number of lossy pages) *
>>     (MAXALIGN(sizeof(HASHELEMENT)) + MAXALIGN(sizeof(PagetableEntry))
>>     + sizeof(Pointer) + sizeof(Pointer)) /
>>     (1024 * 1024).

> I am yet to give more thought on the above formula
> (particularly exact_pages + lossy_pages), but  I was also wondering if the user
> would indeed be able to figure out the above way to estimate the memory, or the
> explain itself should show the estimated memory  required for the bitmap. For
> hash joins we do show the memory taken by the hash table in show_hash_info(). We
> can show the memory requirement in addition to the number of exact/lossy pages.

Thank you for the review!

Reconsidering that, I wish to know your opinion.  The patch shows the number of exact/lossy pages that has been fetched
ina bitmap heap scan.  But the number varies with the fraction of tuples to be retrieved like the following. 

postgres=# EXPLAIN ANALYZE SELECT * FROM demo WHERE col2 between 0.01 and 0.02;
                  QUERY PLAN 

------------------------------------------------------------------------------------------------------------------------------------Bitmap
HeapScan on demo  (cost=2187.35..101419.96 rows=102919 width=42) (actual time=23.684..1302.382 rows=99803 loops=1)
RecheckCond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double precision))  Rows Removed by Index Recheck:
6279502 Heap Blocks: exact=1990 lossy=59593  ->  Bitmap Index Scan on demo_col2_idx  (cost=0.00..2161.62 rows=102919
width=0)(actual time=23.330..23.330 rows=99803 loops=1)        Index Cond: ((col2 >= 0.01::double precision) AND (col2
<=0.02::double precision))Total runtime: 1311.949 ms 
(7 rows)

postgres=# EXPLAIN ANALYZE SELECT * FROM demo WHERE col2 between 0.01 and 0.02 LIMIT 5000;
                                QUERY PLAN 

------------------------------------------------------------------------------------------------------------------------------------------Limit
(cost=2187.35..7008.26 rows=5000 width=42) (actual time=23.543..86.093 rows=5000 loops=1)  ->  Bitmap Heap Scan on demo
(cost=2187.35..101419.96 rows=102919 width=42) (actual time=23.542..85.196 rows=5000 loops=1)        Recheck Cond:
((col2>= 0.01::double precision) AND (col2 <= 0.02::double precision))        Rows Removed by Index Recheck: 312179
  Heap Blocks: exact=99 lossy=2963        ->  Bitmap Index Scan on demo_col2_idx  (cost=0.00..2161.62 rows=102919
width=0)(actual time=23.189..23.189 rows=99803 loops=1)              Index Cond: ((col2 >= 0.01::double precision) AND
(col2<= 0.02::double precision))Total runtime: 86.626 ms 
(8 rows)

So, my question is, we should show the number of exact/lossy pages in a TIDBitmap, not the number of these pages that
hasbeen fetched in the bitmap heap scan? 

Thanks,

Best regards,
Etsuro Fujita