Re: [GENERAL] Performance issue with libpq prepared queries on 9.3 and 9.4

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Nov 13, 2014 at 7:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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