improve transparency of bitmap-only heap scans

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: improve transparency of bitmap-only heap scans
Date: 2019-05-18 19:28:59
Message-ID: CAMkU=1xQcMJgMeNxatR60R+-Bv4TeeO1ZcWmrJS4McLvbBmoXg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
example_output.txt text/plain 1.5 KB
bitmap_only_avoided_v1.patch application/octet-stream 2.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2019-05-18 19:37:38 Re: Statistical aggregate functions are not working with PARTIAL aggregation
Previous Message Tomas Vondra 2019-05-18 19:00:42 Re: Multivariate MCV stats can leak data to unprivileged users