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

On Thu, Oct 31, 2013 at 7:54 PM, Etsuro Fujita
<fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> Hi,
>
> I think that lossy-heap-block information for a bitmap heap scan, not just "Rows
> Removed by Index Recheck" information, would also be a clue used to tune
> work_mem for better performance especially when the bitmap heap scan uses an
> index such as gin or gist, not btree.
>
> So here's a patch that adds the information to the EXPLAIN ANALYZE output. The
> following shows an example. The number of lossy-heap-block fetches (ie
> tbmres->ntuples = -1) as well as that of exact-heap-block fetches (ie
> tbmres->ntuples >= 0) are shown in the "Heap Blocks" line.
>
> postgres=# EXPLAIN ANALYZE SELECT * FROM demo WHERE col2 between 0.01 and 0.02;
> QUERY PLAN
> --------------------------------------------------------------------------------
> ------------------------------------------------
> Bitmap Heap Scan on demo (cost=2716.54..92075.46 rows=105766 width=34) (actual
> time=24.907..1119.961 rows=100047 loops=1)
> Recheck Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double
> precision))
> Rows Removed by Index Recheck: 5484114
> Heap Blocks: exact=11975 lossy=46388
> -> Bitmap Index Scan on demo_idx (cost=0.00..2690.09 rows=105766 width=0)
> (actual time=22.821..22.821 rows=100047 loops=1)
> Index Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double
> precision))
> Total runtime: 1129.334 ms
> (7 rows)
>
> Comments welcome.

This is what I'm looking for! This feature is really useful for tuning work_mem
when using full text search with pg_trgm.

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?

Anyway, could you add the patch into next CF?

Regards,

--
Fujii Masao