Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

Hi,

On 2019-04-29 10:35:39 -0400, Tom Lane wrote:
> This isn't the first time we've seen a plan-choice failure of this sort.
> I've wondered if we should make the plancache simply disbelieve generic
> cost estimates that are actually cheaper than the custom plans, on the
> grounds that they must be estimation errors. In principle a generic
> plan could never really be better than a custom plan; so if it looks
> that way on a cost basis, what that probably means is that the actual
> parameter values are outliers of some sort (e.g. extremely common),
> and the custom plan "knows" that it's going to be taking a hit from
> that, but the generic plan doesn't. In this sort of situation, going
> with the generic plan could be really disastrous, which is exactly
> what the OP is seeing (and what we've seen reported before).
>
> However, I'm not sure how to tune this idea so that it doesn't end up
> rejecting perfectly good generic plans. It's likely that there will be
> some variation in the cost estimates between the generic and specific
> cases, even if the plan structure is exactly the same; and that
> variation could go in either direction.

Yea, I've both seen the "generic is cheaper due to averaged selectivity"
and the "insignificant cost variations lead to always prefer custom
plan" problems in production.

I've also - but less severely - seen that the "planning cost" we add to
the custom plan leads to the generic plan to always be preferred. In
particular for indexed queries, on system that set random_page_cost =
seq_page_cost = 1 (due to SSD or expectation that workload is entirely
cached), the added cost from cached_plan_cost() can be noticable in
comparison to the estimated cost of the total query.

Greetings,

Andres Freund