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
(4810kBdesired) -> 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