On Mon, Aug 7, 2023 at 3:18 PM Peter Geoghegan <p...@bowt.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
