On Mon, Jul 24, 2023 at 10:36 AM Jeff Davis <pg...@j-davis.com> wrote: > I'm getting a bit lost in this discussion as well -- for the purposes > of this feature, we only need to know whether to push down a clause as > an Index Filter or not, right?
I think so. > Could we start out conservatively and push down as an Index Filter > unless there is some other clause ahead of it that can't be pushed > down? That would allow users to have some control by writing clauses in > the desired order or wrapping them in functions with a declared cost. I'm a bit concerned about cases like the one I described from the regression tests. The case in question shows a cheaper plan replacing a more expensive plan -- so it's a win by every conventional measure. But, the new plan is less robust in the sense that I described yesterday: it will be much slower than the current plan when there happens to be many more "thousand = 42" tuples than expected. We have a very high chance of a small benefit (we save repeated index page accesses), but a very low chance of a high cost (we incur many more heap accesses). Which seems less than ideal. One obvious way of avoiding that problem (that's probably overly conservative) is to just focus on the original complaint from Maxim. The original concern was limited to non-key columns from INCLUDE indexes. If you only apply the optimization there then you don't run the risk of generating a path that "out competes" a more robust path in the sense that I've described. This is obviously true because there can't possibly be index quals/scan keys for non-key columns within the index AM. -- Peter Geoghegan
