Re: Use of additional index columns in rows filtering

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

Cool. I'll try to build my own set of examples that I find interesting
either because it's what the patch aims to help with, or because I
expect it to be problematic for some reason. And then we can compare.

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

Yup, I agree with that principle. The AM can evaluate the expression in
a smarter way, without the visibility checks.

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

regards

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