Re: Use of additional index columns in rows filtering

On 8/8/23 20:36, Peter Geoghegan wrote:
> On Tue, Aug 8, 2023 at 11:14 AM Tomas Vondra
> <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>> I agree this patch shouldn't make it harder to improve these cases, but
>> TBH I don't quite see which part of the patch would do that? Which bit
>> are you objecting to? If we decide to change how match_clause_to_index()
>> deals with these cases, to recognize them as index quals, the patch will
>> be working just fine.
>
> Well, I also recently said that I think that it's important that you
> figure out a way to reliably avoid visibility checks, in cases where
> it can be avoided entirely -- since that can lead to huge savings in
> heap accesses. You haven't done that yet, but you really should look
> into it IMV.
>
> Assuming that that happens, then it immediately gives index scans a
> huge advantage over bitmap index scans. At that point it seems
> important to describe (in high level terms) where it is that the
> advantage is innate, and where it's just because we haven't done the
> required work for bitmap index scans. I became confused on this point
> myself yesterday. Admittedly I should have been able to figure it out
> on my own -- but it is confusing.
>

Yeah, I agree that might help a lot, particularly for tables that have a
significant fraction of not-all-visible pages.

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

Right, and I'm not against improving that, but I see it more like an
independent task. I don't think it needs (or should) to be part of this
patch - skipping visibility checks would apply to IOS, while this is
aimed only at plain index scans.

Furthermore, I don't have a very good idea how to do that (except maybe
for relying on the leakproof flag).

regards

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