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
> 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
> 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
   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)


Best regards,
Etsuro Fujita

Attachment: explain-bitmapscan-20131213.patch
Description: Binary data

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to