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

From: "Etsuro Fujita" <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: "'pgsql-hackers'" <pgsql-hackers(at)postgresql(dot)org>
Subject: Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan
Date: 2013-10-31 10:54:31
Message-ID: 002801ced627$93f57a10$bbe06e30$@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Thanks,

Best regards,
Etsuro Fujita

Attachment Content-Type Size
explain-bitmapscan-20131031.patch application/octet-stream 3.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2013-10-31 11:18:15 Re: OSX doesn't accept identical source/target for strcpy() anymore
Previous Message Kyotaro HORIGUCHI 2013-10-31 10:43:10 Get more from indices.