Re: Use of additional index columns in rows filtering

On 8/19/23 02:49, Peter Geoghegan wrote:
> 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.
>

I haven't really thought about INCLUDE columns at all. I agree it seems
doable and possibly quite useful, and doing it outside the index AM
seems about right. The index does not know about the opclass for these
columns, it just stores them, why/how should it be responsible to do
something with it? And as you said, it seems like a (fairly natural?)
extension of the patch discussed in this thread.

That being said, I don't intend to make this work in v1. Once the other
issues get solved in some way, I may try hacking a WIP, but mostly to
try if there's not some design issue that'd make it hard in the future.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company