Re: Use of additional index columns in rows filtering

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

Are you sure you're using the right build? Because I get this plan:

QUERY PLAN
-------------------------------------------------------------------
Index Scan using tenk1_thous_tenthous on tenk1 (cost=0.29..44.48
rows=10 width=250)
Index Cond: (thousand = 42)
Index Filter: (tenthous <> 1)
Filter: (tenthous <> 1)
(4 rows)

Again, the inequality is clearly recognized as index filter.

regards

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