Re: Avoiding bad prepared-statement plans.

On Tuesday 09 February 2010 13:08:54 Jeroen Vermeulen wrote:
> I've been discussing this with Josh, Heikki, and Peter E. over the past
> few weeks.
>
> As Peter observed years ago, prepared statements can perform badly
> because their plans are overly generic. Also, statistics change and
> sometimes plans should change with them. It would be nice if we could
> avoid users having to worry about these things.
>
> I have some ideas that I'm willing to work on, if people agree that
> they're useful. These are simple changes; the goal is to avoid
> pathological performance at minimal cost, not to make prepared
> statements faster than parameterized ones for everyone. The ideas
> interact in various ways.
>
>
> = 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.
>
> = 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:
That is not that easy. It means that you have to use savepoints enclosing each
and every execution of a prepared statement because the query could have
sideeffects. Which wouldnt be terribly efficient...

Andres