Re: The logic behind comparing generic vs. custom plan costs

Justin Blank <justin(dot)blank(at)gmail(dot)com> writes:
> My idea had been that even if the custom plans average higher cost
> than the generic plan, it is still worth considering custom plans. If
> 1 time in 5, the custom plan is 10% of the cost of the generic plan,
> it doesn't matter if the average custom plan is worse, you should at
> least determine the cost of the custom plan before deciding whether to
> use it or the generic plan. Even if 80% of the time, you don't use the
> custom plan, the payoff in the good case may be worth it.

What you'd have to argue is that the average cost of the custom plans
(including the planning time) is less than the average cost of the
generic plan (including planning time to make a custom plan that's
then not used). I'm not exactly convinced that that's any better
than "always use a custom plan" mode: once you've gone to the
trouble of making a custom plan, why not use it?

I'd be the first to agree that this logic is squishy as heck, and
I'd love to find a better way. There are a couple of very nasty
problems to think about, though:

* The estimate of the cost of planning is pretty laughable. It's
not a very detailed estimate, and even if it were there's a scale
problem in comparing it to estimated execution costs. So I'm
hesitant to put any great weight on that estimate.

* Even comparing the estimated costs of custom and generic plans
is full of pitfalls, because they are based on different estimates
of the selectivity of WHERE clauses. We've had reports for instance
of the cache picking a generic plan that was awful for the actual
parameter values, because the generic plan's cost was based on an
assumed-average case, while the custom plans' cost reflected reality
more accurately and hence were higher than the generic estimate even
though in reality they were the better choice. (I don't recall
precise details, but I think it was something like the actual
parameter matching a very common value so that the number of rows
involved would be much greater than the generic plan assumed.)

Without some way to tame those problems, I'm not sure that marginal
tweaks in the plan-selection rule will accomplish a lot.

One idea perhaps could be to pay attention to how much the custom
plans' estimated rowcounts move around, and only switch to a generic
plan if there's not too much change and the generic plan's estimated
rowcounts are in the same range. Or in other words, rather than
believing a cost comparison, what we'd look for is whether we get
basically the same plan in generic mode as in custom mode. I'm
not sure how to mechanize that, though.

regards, tom lane