Re: Use of additional index columns in rows filtering

On Mon, Jul 24, 2023 at 11:37 AM Jeff Davis <pgsql(at)j-davis(dot)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