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? Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
