Re: Improve EXPLAIN output for multicolumn B-Tree Index

From: Jelte Fennema-Nio <postgres(at)jeltef(dot)nl>
To: Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>
Cc: Masahiro(dot)Ikeda(at)nttdata(dot)com, ashutosh(dot)bapat(dot)oss(at)gmail(dot)com, pgsql-hackers(at)lists(dot)postgresql(dot)org, Masao(dot)Fujii(at)nttdata(dot)com
Subject: Re: Improve EXPLAIN output for multicolumn B-Tree Index
Date: 2024-06-24 12:41:53
Message-ID: CAGECzQQ+x2oUupuY4_2MfppptK39MTfbKnWsjkN_38aJbF7zgw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

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

> I don't want A to to be the plan, while showing B' to the user, as the
> performance picture for the two may be completely different. And, as I
> mentioned upthread, the differences between AMs in the (lack of)
> meaning in index column order also makes it quite wrong to generally
> separate prefixes equalities from the rest of the keys.

Yeah, that makes sense. These specific explain lines probably
only/mostly make sense for btree. So yeah we'd want the index AM to be
able to add some stuff to the explain plan.

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ashutosh Bapat 2024-06-24 12:55:35 Re: Improve EXPLAIN output for multicolumn B-Tree Index
Previous Message Nisha Moond 2024-06-24 12:35:07 Re: Conflict detection and logging in logical replication