On Thu, Aug 3, 2023 at 3:04 PM Tomas Vondra <tomas.von...@enterprisedb.com> wrote: > Because my patch is all about reducing the heap pages, which are usually > the expensive part of the index scan. But you're right the "index scan" > with index filter may access more index pages, because it has fewer > "access predicates".
It's not so much the unnecessary index page accesses that bother me. At least I didn't push that aspect very far when I constructed my adversarial test case -- index pages were only a small part of the overall problem. (I mean the problem at runtime, in the executor. The planner expected to save a small number of leaf page accesses, which was kinda, sorta the problem there -- though the planner might have technically still been correct about that, and can't have been too far wrong in any case.) The real problem that my adversarial case seemed to highlight was a problem of extra heap page accesses. The tenk1 table's VM is less than one page in size, so how could it have been VM buffer hits? Sure, there were no "table filters" involved -- only "index filters". But even "index filters" require heap access when the page isn't marked all-visible in the VM. That problem just cannot happen with a similar plan that eliminates the same index tuples within the index AM proper (the index quals don't even have to be "access predicates" for this to apply, either). Of course, we never need to check the visibility of index tuples just to be able to consider eliminating them via nbtree search scan keys/index quals -- and so there is never any question of heap/VM access for tuples that don't pass index quals. Not so for "index filters", where there is at least some chance of accessing the heap proper just to be able to eliminate non-matches. While I think that it makes sense to assume that "index filters" are strictly better than "table filters" (assuming they're directly equivalent in that they contain the same clause), they're not *reliably* any better. So "index filters" are far from being anywhere near as good as an equivalent index qual (AFAICT we should always assume that that's true). This is true of index quals generally -- this advantage is *not* limited to "access predicate index quals". (It is most definitely not okay for "index filters" to displace equivalent "access predicate index quals", but it's also not really okay to allow them to displace equivalent "index filter predicate index quals" -- the latter case is less bad, but AFAICT they both basically aren't acceptable "substitutions".) -- Peter Geoghegan
