One interesting option would be kicking in an extra more expensive
planning cycle after the Nth run of the query, in general a lot of
these planned queries run 1000s of times, if you add some extra cost
to run 100 it may not be prohibitive cost wise.
On Tue, Nov 18, 2014 at 8:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Thu, Nov 13, 2014 at 7:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> One thing that occurs to me is that if the generic plan estimate comes
>>> out much cheaper than the custom one, maybe we should assume that the
>>> generic's cost estimate is bogus. Right offhand I can't think of a reason
>>> for a custom plan to look worse than a generic one, unless there's a
>>> statistical quirk like this one.
>
>> That's an interesting idea, but what do we do after deciding that it's
>> bogus?
>
> Keep using custom plans. It's possible that the estimate that's in error
> is the custom one, but that's not the way to bet IMO, since the custom
> plan estimate is based on better information.
>
>> The generic plan really can't be cheaper than the custom plan,
>> but it could be the same price, or as close as makes no difference.
>
> Right, and what we want to do is use the generic plan as long as it's
> close to the same cost (close enough to not justify replanning effort).
> The trick here is to not be fooled by estimation errors. Can we assume
> that generic cost < custom cost is always an estimation error?
>
> Another idea that occurred to me is to run a planning cycle in which the
> actual parameter values are made available to the planner, but as
> estimates not hard constants (this facility already exists, it's just not
> being used by plancache.c). This would yield cost estimates that are more
> safely comparable to the custom plan. But I'm not sure that we'd want to
> expend yet another planning cycle to do this, nor am I sure that we'd want
> to use such a plan as The Generic Plan.
>
> regards, tom lane
