On Mon, Jul 24, 2023 at 11:37 AM Jeff Davis <pg...@j-davis.com> wrote: > I see. You're concerned that lowering the cost of an index scan path > too much, due to pushing down a clause as an Index Filter, could cause > it to out-compete a more "robust" plan.
The optimizer correctly determines that 3 index scans (plus a bitmap OR node) are more expensive than 1 very similar index scan. It's hard to argue with that. > That might be true but I'm not sure what to do about that unless we > incorporate some "robustness" measure into the costing. If every > measure we have says one plan is better, don't we have to choose it? I'm mostly concerned about the possibility itself -- it's not a matter of tuning the cost. I agree that that approach would probably be hopeless. There is a principled (albeit fairly involved) way of addressing this. The patch allows the optimizer to produce a plan that has 1 index scan, that treats the first column as an index qual, and the second column as a filter condition. There is no fundamental reason why we can't just have 1 index scan that makes both columns into index quals (instead of 3 highly duplicative variants of the same index scan). That's what I'm working towards right now. > If I understand correctly, you mean we couldn't use an Index Filter on > a key column? That seems overly restrictive, there are plenty of > clauses that might be useful as an Index Filter but cannot be an Index > Cond for one reason or another. I think that you're probably right about it being overly restrictive -- that was just a starting point for discussion. Perhaps there is an identifiable class of clauses that can benefit, but don't have the downside that I'm concerned about. -- Peter Geoghegan
