Re: Use of additional index columns in rows filtering

On Thu, Aug 17, 2023 at 4:29 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Wed, 2023-08-09 at 17:14 -0700, Peter Geoghegan wrote:
> > + Index quals are better than equivalent index filters because bitmap
> > index scans can only use index quals
>
> It seems there's consensus that:
>
> * Index Filters (Tomas's patch and the topic of this thread) are more
> general, because they can work on any expression, e.g. 1/x, which can
> throw an error if x=0.

Agreed, but with one small caveat: they're not more general when it
comes to bitmap index scans, which seem like they'll only ever be able
to support index quals. But AFAICT that's the one and only exception.

> * Index quals are more optimizable, because such operators are not
> supposed to throw errors or have side effects, so we can evaluate them
> before determining visibility.

Right. Though there is a second reason: the index AM can use them to
navigate the index more efficiently with true index quals. At least in
the case of SAOPs, and perhaps in other cases in the future.

> I wouldn't describe one as "better" than the other, but I assume you
> meant "more optimizable".

The use of the term "better" is actually descriptive of Tomas' patch.
It is structured in a way that conditions the use of index filters on
the absence of equivalent index quals for eligible/indexed clauses. So
it really does make sense to think of it as a "qual hierarchy" (to use
Tomas' term).

It's also true that it will always be fundamentally impossible to use
index quals for a significant proportion of all clause types, so
"better when feasible at all" is slightly more accurate.

> Is there any part of the design here that's preventing this patch from
> moving forward? If not, what are the TODOs for the current patch, or is
> it just waiting on more review?

Practically speaking, I have no objections to moving ahead with this.
I believe that the general structure of the patch makes sense, and I
don't expect Tomas to do anything that wasn't already expected before
I chimed in.

--
Peter Geoghegan