On Mon, Aug 7, 2023 at 9:21 PM Peter Geoghegan <p...@bowt.ie> wrote: > 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.
The approach in your patch can only really work with index scans (and index-only scans). So while it is more general than true index quals in some ways, it's also less general in other ways: it cannot help bitmap index scans. While I accept that the inability of bitmap index scans to use index filters in this way is, to some degree, a natural and inevitable downside of bitmap index scans, that isn't always true. For example, it doesn't seem to be the case with simple inequalities. Bitmap index scans argue for making cases involving quals that are "index quals in spirit" into actual index quals. Even if you can reliably avoid extra heap accesses for plain index scans using expression evaluation, I can't see that working for bitmap index scans. More concretely, if we have an index on "tenk1 (four, two)", then we miss out on the opportunity to eliminate heap accesses for a query like this one: select ctid, * from tenk1 where four = 1 and two != 1; This will get a bitmap index scan plan (that uses our composite index), which makes sense overall. But the details beyond that make no sense -- since we're using table filter quals for "two". It turns out that the bitmap heap scan will access every single heap page in the tenk1 table as a result, even though we could have literally avoided all heap accesses had we been able to push down the != as an index qual. This is a difference in "buffers hit" that is close to 2 orders of magnitude. I'd be okay with treating these cases as out of scope for this patch, but we should probably agree on the parameters. The patch certainly shouldn't make it any harder to fix cases such as this. -- Peter Geoghegan
