Re: Improve EXPLAIN output for multicolumn B-Tree Index

On Mon, 24 Jun 2024 at 14:42, Jelte Fennema-Nio <postgres(at)jeltef(dot)nl> wrote:
>
> On Mon, 24 Jun 2024 at 13:02, Matthias van de Meent
> <boekewurm+postgres(at)gmail(dot)com> wrote:
> > It does not really behave similar: index scan keys (such as the
> > id3=101 scankey) don't require visibility checks in the btree code,
> > while the Filter condition _does_ require a visibility check, and
> > delegates the check to the table AM if the scan isn't Index-Only, or
> > if the VM didn't show all-visible during the check.
>
> Any chance you could point me in the right direction for the
> code/docs/comment about this? I'd like to learn a bit more about why
> that is the case, because I didn't realize visibility checks worked
> differently for index scan keys and Filter keys.

This can be derived by combining how Filter works (it only filters the
returned live tuples) and how Index-Only scans work (return the index
tuple, unless !ALL_VISIBLE, in which case the heap tuple is
projected). There have been several threads more or less recently that
also touch this topic and closely related topics, e.g. [0][1].

> > Furthermore, the index could use the scankey to improve the number of
> > keys to scan using "skip scans"; by realising during a forward scan
> > that if you've reached tuple (1, 2, 3) and looking for (1, _, 1) you
> > can skip forward to (1, 3, _), rather than having to go through tuples
> > (1, 2, 4), (1, 2, 5), ... (1, 2, n). This is not possible for
> > INCLUDE-d columns, because their datatypes and structure are opaque to
> > the index AM; the AM cannot assume anything about or do anything with
> > those values.
>
> Does Postgres actually support this currently? I thought skip scans
> were not available (yet).

Peter Geoghegan has been working on it as project after PG17's
IN()-list improvements were committed, and I hear he has the basics
working but the further details need fleshing out.

> > As you can see, there's a huge difference in performance. Putting both
> > non-bound and "normal" filter clauses in the same Filter clause will
> > make it more difficult to explain performance issues based on only the
> > explain output.
>
> Fair enough, that's of course the main point of this patch in the
> first place: being able to better interpret the explain plan when you
> don't have access to the schema. Still I think Filter is the correct
> keyword for both, so how about we make it less confusing by making the
> current "Filter" more specific by calling it something like "Non-key
> Filter" or "INCLUDE Filter" and then call the other something like
> "Index Filter" or "Secondary Bound Filter".

I'm not sure how debuggable explain plans are without access to the
schema, especially when VERBOSE isn't configured, so I would be
hesitant to accept that as an argument here.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)

[0] https://www.postgresql.org/message-id/flat/N1xaIrU29uk5YxLyW55MGk5fz9s6V2FNtj54JRaVlFbPixD5z8sJ07Ite5CvbWwik8ZvDG07oSTN-usENLVMq2UAcizVTEd5b-o16ZGDIIU%3D%40yamlcoder.me
[1] https://www.postgresql.org/message-id/flat/cf85f46f-b02f-05b2-5248-5000b894ebab%40enterprisedb.com