Re: Avoiding bad prepared-statement plans.

On 09/02/10 12:08, Jeroen Vermeulen wrote:
>
> = Projected-cost threshold =

[snip - this is the simple bit. Sounds very sensible. ]

> = Actual-cost threshold =
>
> Also stop using the generic plan if the statement takes a long time to
> run in practice.

Do you mean:
1. Rollback the current query and start again
2. Mark the plan as a bad one and plan again next execute

If you can figure out how to do #1 then you could probably do it for all
queries, but I'm guessing it's far from simple to implement.

> = 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.

Presumably some score based on update stats and vacuum activity etc.

The good side of all these ideas is good indeed. The bad side is plan
instability. Someone somewhere will have a generic plan that turns out
better than the specific plan (due to bad stats or config settings or
just planner limitations). The question is (I guess): How many more
winners will there be than losers?

--
Richard Huxton
Archonet Ltd