Re: Use of additional index columns in rows filtering

On Wed, Aug 9, 2023 at 10:00 AM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> Anyway, I find this discussion rather abstract and I'll probably forget
> half the important cases by next week. Maybe it'd be good to build a set
> of examples demonstrating the interesting cases? We've already used a
> couple tenk1 queries for that purpose ...

That seems wise. I'll try to come up with a list of general principles
with specific and easy to run examples later on today.

> I'm trying to make the patch to not dependent on such change. In a way,
> once a clause gets recognized as index qual, it becomes irrelevant for
> my patch. But the patch also doesn't get any simpler, because it still
> needs to do the same thing for the remaining quals.

Practically speaking, I don't see any reason why you won't be able to
sign off on the set of principles that I'll lay out for work in this
area, while at the same time continuing with this patch more or less
as originally planned.

At one point I thought that your patch might be obligated to
compensate for its tendency to push down OR-heavy clauses as
expressions, leading to "risky" plans. While I still have a concern
about that now, I'm not going to try to make it your problem. I'm now
inclined to think of this as an existing problem, that your patch will
increase the prevalence of -- but not to the extent that it makes
sense to hold it up. To some extent it is up to me to put my money
where my mouth is.

I'm optimistic about the prospect of significantly ameliorating (if
not fixing) the "risky OR expression plan" problem in the scope of my
work on 17. But even if that doesn't quite happen (say we don't end up
normalizing to CNF in the way that I've suggested for 17), we should
at least reach agreement on the best way forward. If we could just
agree that evaluating complicated OR expressions in index filters is
much worse than finding a way to pass them down as an equivalent index
qual (an SAOP), then I could live with it for another release or two.

As I said, I mostly just care about having the right general
principles in place at this point.

> OTOH if there was some facility to decide if a qual is "safe" to be
> executed on the index tuple, that'd be nice. But as I already said, I
> see it more as an additional optimization, as it only applies to a
> subset of cases.

I'm happy to go with that.

--
Peter Geoghegan