Re: BUG #17560: Planner can not find plan with lowest cost

On Fri, Jul 29, 2022 at 3:16 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> and the reason that it is awful is that the planner is not aware of
> the 100% correlation between the contents of the tables, specifically
> the fact that the v1 and v2 join conditions add precisely zero
> selectivity beyond the id join condition.

Yeah, and the planner thinks the three join conditions have independent
probabilities and estimates them by taking the product of their
selectivities.

> BTW, the fact that it isn't noticing the marginally-cheaper-estimated-
> cost plan isn't a bug either. We intentionally treat plans of essentially
> equal cost as redundant and keep only one, in order to reduce planning
> time. I've not traced through this example in detail, but I'm sure that
> some pruning step of that kind eliminated consideration of the slightly
> cheaper plan. The fact that in reality it's a *lot* cheaper escapes
> the planner because of the poor selectivity estimates.

Right. The planner does fuzzy cost comparison with a factor
STD_FUZZ_FACTOR. So they are fuzzily the same on total cost, while the
second plan is fuzzily worse on startup.

Thanks
Richard