Re: Use of additional index columns in rows filtering

On Tue, Aug 8, 2023 at 11:36 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> > The only thing the patch does is it looks at clauses we decided not to
> > treat as index quals, and do maybe still evaluate them on index. And I
> > don't think I want to move these goalposts much further.
>
> Avoiding the need for visibility checks completely (in at least a
> subset of cases) was originally your idea. I'm not going to insist on
> it, or anything like that. It just seems like something that'll add a
> great deal of value over what you have already.

Another idea in this area occurred to me today: it would be cool if
non-key columns from INCLUDE indexes could completely avoid visibility
checks (not just avoid heap accesses using the visibility map) in
roughly the same way that we'd expect with an equivalent key column
already, today (if it was an index filter index qual). Offhand I think
that it would make sense to do that outside of index AMs, by extending
the mechanism from Tomas' patch to this special class of expression.
We'd invent some other class of index filter that "outranks"
conventional index filters when the optimizer can safely determine
that they're "index filters with the visibility characteristics of
true index quals". I am mostly thinking of simple, common cases here
(e.g., an equality operator + constant).

This might require the involvement of the relevant btree opclass,
since that's where the no-visibility-check-safety property actually
comes from. However, it wouldn't need to be limited to INCLUDE B-Tree
indexes. You could for example do this with a GiST INCLUDE index that
had no opclass information about the datatype/operator. That'd be a
natural advantage of an approach based on index filters.

--
Peter Geoghegan