Re: Improve EXPLAIN output for multicolumn B-Tree Index

On Thu, Jun 27, 2024 at 11:06 PM <Masahiro(dot)Ikeda(at)nttdata(dot)com> wrote:
> OK. I would like to understand more about your proposed patch. I
> have also registered as a reviewer in the commitfests entry.

Great!

> 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.

That makes sense.

The goal of your patch is to highlight when an index scan is using an
index that is suboptimal for a particular query (a query that the user
runs through EXPLAIN or EXPLAIN ANALYZE). The underlying rules that
determine "access predicate vs. filter predicate" are not very
complicated -- they're intuitive, even. But even an expert can easily
make a mistake on a bad day.

It seems to me that all your patch really needs to do is to give the
user a friendly nudge in that direction, when it makes sense to. You
want to subtly suggest to the user "hey, are you sure that the index
the plan uses is exactly what you expected?". Fortunately, even when
skip scan works well that should still be a useful nudge. If we assume
that the query that the user is looking at is much more important than
other queries, then the user really shouldn't be using skip scan in
the first place. Even a good skip scan is a little suspicious (it's
okay if it "stands out" a bit).

> 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.

That seems logical.

--
Peter Geoghegan