Re: Avoiding bad prepared-statement plans.

Tom Lane wrote:
> 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.

Ideally we would do late binding (bind on the first supplied parameters,
like we do for unnamed protocol prepared queries now), and then replan
if the statistics for later parameters significantly differ from the
ones used for the the initial planning.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +