On Thu, Oct 31, 2013 at 7:54 PM, Etsuro Fujita
<fujita.ets...@lab.ntt.co.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?


Fujii Masao

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

Reply via email to