Re: Avoiding bad prepared-statement plans.

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Feb 9, 2010 at 7:08 AM, Jeroen Vermeulen <jtv(at)xs4all(dot)nl> wrote:
>> Periodically re-plan prepared statements on EXECUTE. This is also a chance
>> for queries that were being re-planned every time to go back to a generic
>> plan.

> The most common problem here seems to be that (some?) MCVs need
> different treatment than non-MCVs, so I don't think periodically
> replanning is going to help very much.

It won't help at all. The only reason for replanning is if something
about the schema or the statistics change, and we already have got
automatic cached-plan invalidation in both those cases. If you replan
simply because some time has elapsed, you'll just get exactly the
same plan.

The only case that I think still has any merit is where you get a
significantly better plan with known parameter values than without.
The projected-cost threshold might be a reasonable approach for
attacking that, ie, if estimated cost of generic plan exceeds X
then take the time to build a custom plan instead. I'm not sure that
really will fix the problem, but it would be a very simple change to
make to see how much it helps people.

regards, tom lane