Re: Improve EXPLAIN output for multicolumn B-Tree Index

On Thu, 27 Jun 2024 at 22:02, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> It's also possible that we should just do something simple, like your
> patch, even though technically it won't really be accurate in cases
> where skip scan is used to good effect. Maybe showing the "default
> working assumption" about how the scan keys/clauses will behave at
> runtime is actually the right thing to do. Maybe I am just
> overthinking it.

IIUC, you're saying that your skip scan will improve the situation
Masahiro describes dramatically in some/most cases. But it still won't
be as good as a pure index "prefix" scan.

If that's the case then I do think you're overthinking this a bit.
Because then you'd still want to see this difference between the
prefix-scan keys and the skip-scan keys. I think the main thing that
the introduction of the skip scan changes is the name that we should
show, e.g. instead of "Non-key Filter" we might want to call it "Skip
Scan Cond"

I do think though that in addition to a "Skip Scan Filtered" count for
ANALYZE, it would be very nice to also get a "Skip Scan Skipped" count
(if that's possible to measure/estimate somehow). This would allow
users to determine how effective the skip scan was, i.e. were they
able to skip over large swaths of the index? Or did they skip over
nothing because the second column of the index (on which there was no
filter) was unique within the table