Re: Use of additional index columns in rows filtering

On 8/8/23 00:18, Peter Geoghegan wrote:
> 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.
> 

Yeah. It's be much easier if the decision was done in one place, without
then changing it later.

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

Agreed.

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

Are you sure? Because if I try with the 20230716 patch, I get this plan
(after disabling bitmapscan):

                             QUERY PLAN
-------------------------------------------------------------------
 Index Scan using tenk1_thous_tenthous on tenk1  (cost=0.31..44.54
rows=10 width=250)
   Index Cond: (thousand = 42)
   Index Filter: (tenthous <> ALL
('{1,3,42,43,44,45,46,47,48,49,50}'::integer[]))
   Filter: (tenthous <> ALL ('{1,3,42,43,44,45,46,47,48,49,50}'::integer[]))
(4 rows)

So the condition is recognized as index filter. Or did you mean
something different?

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

Not sure. A couple messages back I suggested that maybe there is a way
to check which expression would be safe to evaluate before checking the
visibility. This seems similar, although what you're suggesting really
applies to the "transformed" SAOP, and I'm not sure it can be extended
to the original SAOP.


regards

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