Re: Use of additional index columns in rows filtering

On Mon, Aug 7, 2023 at 12:34 PM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> On 8/7/23 02:38, Peter Geoghegan wrote:
> > This plan switchover isn't surprising in itself -- it's one of the most
> > important issues addressed by my SAOP patch. However, it *is* a little
> > surprising that your patch doesn't even manage to use "Index Filter"
> > quals. It appears that it is only capable of using table filter quals.
> > Obviously, the index has all the information that expression
> > evaluation needs, and yet I see "Filter: (tenk1.tenthous = ANY
> > ('{1,3,42,43,44,45,46,47,48,49,50}'::integer[]))". So no improvement
> > over master here.

> Right. This happens because the matching of SAOP to indexes happens in
> multiple places. Firstly, create_index_paths() matches the clauses to
> the index by calling
>
> match_restriction_clauses_to_index
> -> match_clauses_to_index
> -> match_clause_to_index
>
> Which is where we also decide which *unmatched* clauses can be filters.
> And this *does* match the SAOP to the index key, hence no index filter.
>
> But then we call get_index_paths/build_index_path a little bit later,
> and that decides to skip "lower SAOP" (which seems a bit strange,
> because the column is "after" the equality, but meh). Anyway, at this
> point we already decided what's a filter, ignoring the index clauses,
> and not expecting any backsies.
>
> The simples fix seems to be to add these skipped SAOP clauses as
> filters. We know it can be evaluated on the index ...

Update on this: I recently posted v2 of my patch, which completely
removes build_index_paths's "skip_lower_saop" mechanism. This became
possible in v2 because it fully eliminated all of the advantages that
SOAP style filter quals might have had over true index quals, through
further enhancements on the nbtree side. There is simply no reason to
generate alternative index paths with filter quals in the first place.
(As I seem to like to say, "choice is confusion".)

In short, v2 of my patch fully adheres to the principles set out in
the "qual hierarchy" doc. The planner no longer needs to know anything
about how nbtree executes SAOP index quals, except when costing them.
To the planner, there is pretty much no difference between "=" and "=
ANY()" (for index AMs that natively support SAOP execution).

I imagine that this general planner structure will be ideal for your
patch. If I'm not mistaken, it will allow you to completely avoid
treating SAOPs as a special case. Although the build_index_paths
"skip_lower_saop" thing might have created issues for the approach
your patch takes in the planner, that seems to me to work best as an
argument against the "skip_lower_saop" mechanism -- it was always a
kludge IMV.

--
Peter Geoghegan