Re: Use of additional index columns in rows filtering

On Mon, 2023-07-24 at 10:54 -0700, Peter Geoghegan wrote:
> 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.

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.

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?

> 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.

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.

Regards,
Jeff Davis