RE: Improve EXPLAIN output for multicolumn B-Tree Index

On 2024-06-29 03:27, Peter Geoghegan wrote:
> On Thu, Jun 27, 2024 at 11:06 PM <Masahiro(dot)Ikeda(at)nttdata(dot)com> wrote:
>> 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).

Yes, you're right. I'd like users to take the chance easily.

--
Masahiro Ikeda
NTT DATA CORPORATION