Re: Automatic function replanning

On Thursday 2005-12-22 14:28, Lukas Kahwe Smith wrote:
> Bruce Momjian wrote:
> > Right, if the cardinality changes, you realize this before execution and
> > optimize/save the plan again.  A further optimization would be to save
> > _multiple_ plans for a single prepared plan based on constants and
> > choose one of the other, but that is beyond where we are willing to
> > consider at this stage, I think.
>
> ok .. so you store the cardinality that was used when generating the
> original plan. on the next execution you look up the cardinality again
> and compare it, if its off too much, you replan. however this could in
> extreme cases mean that you replan on every execution and thereby
> killing off the entire advantage of storing the plan. but thats the
> absolute worse case scenario.
>
> regards,
> Lukas
>
> PS: bruce original email was only send to me directly ..

So you have a parameterized query (one parameter for simplicity of argument), 
as the parameter changes, cardinality changes dramatically.

It seems to me that in this case better than replanning is building a data 
structure that associates different parameter values with appropriate plans.  
The plans can be reused until, as would be the case with an no-parameter 
query, a parameter specific plan should be flushed (or the entire family of 
plans can be flushed).