On Mon, 24 Jun 2024 at 13:02, Matthias van de Meent <boekewurm+postg...@gmail.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".
