Re: Avoiding bad prepared-statement plans.

On Tue, Feb 9, 2010 at 7:08 AM, Jeroen Vermeulen <jtv(at)xs4all(dot)nl> wrote:
> = Projected-cost threshold =
>
> If a prepared statement takes parameters, and the generic plan has a high
> projected cost, re-plan each EXECUTE individually with all its parameter
> values bound.  It may or may not help, but unless the planner is vastly
> over-pessimistic, re-planning isn't going to dominate execution time for
> these cases anyway.

How high is high?

> = Actual-cost threshold =
>
> Also stop using the generic plan if the statement takes a long time to run
> in practice.  Statistics may have gone bad.  It could also be a one-off due
> to a load peak or something, but that's handled by:
>
> = Plan refresh =
>
> 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. What might help is something
like plan twice, once assuming you have the most common MCV and once
assuming you have a non-MCV. If the two plans are same, you're
probably safe. Or if you can somehow determine that one of the plans
will still be pretty fast in the other case, you can just use that
plan across the board. Otherwise, you have to somehow vary the plan
based on the actual parameter value.

...Robert