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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073