Re: Use of additional index columns in rows filtering

On Mon, Aug 7, 2023 at 12:34 PM Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:
> 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.

I'm not surprised that it's due to the issue around "lower SAOP"
clauses within get_index_paths/build_index_path. That whole approach
seems rather ad-hoc to me. As you probably realize already, my own
patch has to deal with lots of issues in the same area.

> The simples fix seems to be to add these skipped SAOP clauses as
> filters. We know it can be evaluated on the index ...

Right. Obviously, my preferred solution to the problem at hand is to
make everything into index quals via an approach like the one from my
patch -- that works sensibly, no matter the length of the SAOP arrays.
But even if you're willing to assume that that work will be in place
for 17, there are still certain remaining gaps, that also seem
important.

Even my patch cannot always make SAOP clauses into index quals. There
are specific remaining gaps that I hope that your patch will still
cover. The simplest example is a similar NOT IN() inequality, like
this:

select
  ctid, *
from
  tenk1
where
  thousand = 42
  and
  tenthous not in (1, 3, 42, 43, 44, 45, 46, 47, 48, 49, 50);

There is no way that my patch can handle this case. Where your patch
seems to be unable to do better than master here, either -- just like
with the "tenthous in ( )" variant. Once again, the inequality SAOP
also ends up as table filter quals, not index filter quals.

It would also be nice if we found a way of doing this, while still
reliably avoiding all visibility checks (just like "real index quals"
will) -- since that should be safe in this specific case.

The MDAM paper describes a scheme for converting NOT IN() clauses into
DNF single value predicates. But that's not going to happen for 17,
and doesn't seem all that helpful with a query like this in any case.
But it does suggest an argument in favor of visibility checks not
being truly required for SAOP inequalities like this one (when they
appear in index filters). I'm not sure if that idea is too particular
to SAOP inequalities to be interesting -- just a suggestion.

--
Peter Geoghegan