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

From: "Etsuro Fujita" <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: "'Etsuro Fujita'" <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>, "'Robert Haas'" <robertmhaas(at)gmail(dot)com>
Cc: "'Amit Khandekar'" <amit(dot)khandekar(at)enterprisedb(dot)com>, "'pgsql-hackers'" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan
Date: 2013-12-13 08:40:50
Message-ID: 008101cef7df$06e8a280$14b9e780$@etsuro@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I wrote:
> Robert Haas wrote:
> > I'd be wary of showing a desired value unless it's highly likely to be
> > accurate.

> The desired value is accurately estimated based on (a) the total number
> of exact/lossy pages stored in the TIDBitmap and (b) the following
equation
> in tbm_create(), except for the GIN case where lossy pages are added to
> the TIDBitmap by tbm_add_page().

> /*
> * Estimate number of hashtable entries we can have within maxbytes.
...
> */
> nbuckets = maxbytes /
> (MAXALIGN(sizeof(HASHELEMENT)) +
> MAXALIGN(sizeof(PagetableEntry))
> + sizeof(Pointer) + sizeof(Pointer));

> In the GIN case, however, the version under development has a risk of the
> overestimation. (And in that case, in my understanding, we can't
guarantee
> non-lossy storage of the TIDBitmap any more.) So, for that case, I think
> to change the message for the desired value a bit. I'll submit the patch
> later.

On second thoughts, I've modified the patch so that the EXPLAIN ANALYZE
command shows not only the desired value but the total number of exact/lossy
heap blocks that have been fetched in query execution because ISTM the
latter is also useful for tuning work_mem, when an available memory capacity
is not so large as the desired value, or when non-lossy storage of the
TIDBitmap can't be guaranteed as mentioned above. Here is an example.
Attached is an updated version of the patch, though a sufficient test hasn't
been performed.

postgres=# EXPLAIN ANALYZE SELECT * FROM demo WHERE col2 between 0.01 and
0.02 ;
QUERY PLAN
----------------------------------------------------------------------------
-------------------------------------------------------
Bitmap Heap Scan on demo (cost=2072.10..100674.45 rows=97528 width=42)
(actual time=27.387..1677.511 rows=99833 loops=1)
Recheck Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double
precision))
Rows Removed by Index Recheck: 5581690
Heap Blocks: exact=8585 lossy=52980
Bitmap Memory Usage: 1025kB (4810kB desired)
-> Bitmap Index Scan on demo_col2_idx (cost=0.00..2047.71 rows=97528
width=0) (actual time=25.884..25.884 rows=99833 loops=1)
Index Cond: ((col2 >= 0.01::double precision) AND (col2 <=
0.02::double precision))
Total runtime: 1687.047 ms
(8 rows)

Thanks,

Best regards,
Etsuro Fujita

Attachment Content-Type Size
explain-bitmapscan-20131213.patch application/octet-stream 11.7 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dilip kumar 2013-12-13 08:49:18 Re: Logging WAL when updating hintbit
Previous Message David Rowley 2013-12-13 07:58:24 Re: logical changeset generation v6.8