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:
> Unfortunately, my patch will make the situation more complicated
> for your patch. I would like to resolve the tension between the
> two patches, but I'm not sure how to do that.

OK. I would like to understand more about your proposed patch. I
have also registered as a reviewer in the commitfests entry.

On 2024-06-28 07:40, Peter Geoghegan wrote:
> On Thu, Jun 27, 2024 at 4:46 PM Jelte Fennema-Nio <postgres(at)jeltef(dot)nl> wrote:
>> 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 overx
>> nothing because the second column of the index (on which there was no
>> filter) was unique within the table
>
> Yeah, EXPLAIN ANALYZE should probably be showing something about
> skipping. That provides us with a way of telling the user what really
> happened, which could help when EXPLAIN output alone turns out to be
> quite misleading.
>
> In fact, that'd make sense even today, without skip scan (just with
> the 17 work on nbtree SAOP scans). Even with regular SAOP nbtree index
> scans, the number of primitive scans is hard to predict, and quite
> indicative of what's really going on with the scan.

I agree as well.

Although I haven't looked on your patch yet, if it's difficult to know
how it can optimize during the planning phase, it's enough for me to just
show "Skip Scan Cond (or Non-Key Filter)". This is because users can
understand that inefficient index scans *may* occur.

If users want more detail, they can execute "EXPLAIN ANALYZE". This will
allow them to understand the execution effectively and determine if there
is any room to optimize the plan by looking at the counter of
"Skip Scan Filtered (or Skip Scan Skipped)".

In terms of the concept of EXPLAIN output, I thought that runtime partition
pruning is similar. "EXPLAIN without ANALYZE" only shows the possibilities and
"EXPLAIN ANALYZE" shows the actual results.

Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION