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
explain-bitmapscan-20131213.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
