Re: Use of additional index columns in rows filtering

On Mon, Aug 7, 2023 at 3:18 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> 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.

Actually, this isn't limited to SAOP inequalities. It appears as if
*any* simple inequality has the same limitation. So, for example, the
following query can only use table filters with the patch (never index
filters):

select
ctid, *
from
tenk1
where
thousand = 42 and tenthous != 1;

This variant will use index filters, as expected (though with some
risk of heap accesses when VM bits aren't set):

select
ctid, *
from
tenk1
where
thousand = 42 and tenthous is distinct from 1;

Offhand I suspect that it's a similar issue to the one you described for SAOPs.

I see that get_op_btree_interpretation() will treat != as a kind of
honorary member of an opfamily whose = operator has our != operator as
its negator. Perhaps we should be finding a way to pass != quals into
the index AM so that they become true index quals (obviously they
would only be index filter predicates, never access predicates). That
has the advantage of working in a way that's analogous to the way that
index quals already avoid visibility checks.

--
Peter Geoghegan