Re: Automatic function replanning

Trent Shipley wrote:
> 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).

TODO updated:* Flush cached query plans when the dependent objects change,  when the cardinality of parameters changes
dramatically,or  when new ANALYZE statistics are available  A more complex solution would be to save multiple plans for
different cardinality and use the appropriate plan based on the EXECUTE values.
 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073