On Mon, Aug 7, 2023 at 9:21 PM Peter Geoghegan <pg@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