| 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: | Whole Thread | Raw Message | 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 |
| 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. |