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