When bitmap-only heap scans were introduced in v11 (7c70996ebf0949b142a99) no changes were made to "EXPLAIN". This makes the feature rather opaque. You can sometimes figure out what is going by the output of EXPLAIN (ANALYZE, BUFFERS), but that is unintuitive and fragile.
Looking at the discussion where the feature was added, I think changing the
EXPLAIN just wasn't considered.
The attached patch adds "avoided" to "exact" and "lossy" as a category
under
"Heap Blocks". Also attached is the example output, as the below will
probably wrap to the point of illegibility:
explain analyze select count(*) from foo where a=35 and d between 67 and
70;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=21451.36..21451.37 rows=1 width=8) (actual
time=103.955..103.955 rows=1 loops=1)
-> Bitmap Heap Scan on foo (cost=9920.73..21442.44 rows=3570 width=0)
(actual time=100.239..103.204 rows=3950 loops=1)
Recheck Cond: ((a = 35) AND (d >= 67) AND (d <= 70))
Heap Blocks: avoided=3718 exact=73
-> BitmapAnd (cost=9920.73..9920.73 rows=3570 width=0) (actual
time=98.666..98.666 rows=0 loops=1)
-> Bitmap Index Scan on foo_a_c_idx (cost=0.00..1682.93
rows=91000 width=0) (actual time=28.541..28.541 rows=99776 loops=1)
Index Cond: (a = 35)
-> Bitmap Index Scan on foo_d_idx (cost=0.00..8235.76
rows=392333 width=0) (actual time=66.946..66.946 rows=399003 loops=1)
Index Cond: ((d >= 67) AND (d <= 70))
Planning Time: 0.458 ms
Execution Time: 104.487 ms
I think the name of the node should also be changed to "Bitmap Only Heap
Scan", but I didn't implement that as adding another NodeTag looks like a
lot of tedious error prone work to do before getting feedback on whether
the change is desirable in the first place, or the correct approach.
Cheers,
Jeff
create table foo as select floor(random()*100)::int as a,
floor(random()*100)::int as b, floor(random()*100)::int as c,
floor(random()*100)::int as d, floor(random()*100)::int as e from
generate_series(1,10000000);
vacuum ANALYZE ;
create index on foo (a,c);
create index on foo (d);
update foo set d=d+1 where a=35 and c = 70;
explain analyze select count(*) from foo where a=35 and d between 67 and 70;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=21451.36..21451.37 rows=1 width=8) (actual
time=103.955..103.955 rows=1 loops=1)
-> Bitmap Heap Scan on foo (cost=9920.73..21442.44 rows=3570 width=0)
(actual time=100.239..103.204 rows=3950 loops=1)
Recheck Cond: ((a = 35) AND (d >= 67) AND (d <= 70))
Heap Blocks: avoided=3718 exact=73
-> BitmapAnd (cost=9920.73..9920.73 rows=3570 width=0) (actual
time=98.666..98.666 rows=0 loops=1)
-> Bitmap Index Scan on foo_a_c_idx (cost=0.00..1682.93
rows=91000 width=0) (actual time=28.541..28.541 rows=99776 loops=1)
Index Cond: (a = 35)
-> Bitmap Index Scan on foo_d_idx (cost=0.00..8235.76
rows=392333 width=0) (actual time=66.946..66.946 rows=399003 loops=1)
Index Cond: ((d >= 67) AND (d <= 70))
Planning Time: 0.458 ms
Execution Time: 104.487 ms
bitmap_only_avoided_v1.patch
Description: Binary data
