Re: Automatic function replanning

Trent Shipley wrote:
> On Saturday 2005-12-17 16:28, Lukas Smith wrote:
> > Bruce Momjian wrote:
> > > * Flush cached query plans when the dependent objects change,
> > > when the cardinality of parameters changes dramatically, or
> > > when new ANALYZE statistics are available
> >
> > Wouldn't it also make sense to flush a cached query plan when after
> > execution it is determined that one or more assumptions that the cached
> > query plan was based on was found to be off? Like the query plan was
> > based on the assumption that a particular table would only return a hand
> > full of rows, but in reality it returned a few thousand.
> >
> > regards,
> > Lukas
> >
>
> Proposed rewrite
>
> * Mark query plan for flush (opportunistic replan) when:
> ** dependent objects change,
> ** cardinality of parameters changes sufficiently (per planner parameter)
> ** when new ANALYZE statistics are available and per planner parameter differ
> sufficiently from prior statistics.

Frankly, I think any new ANALYZE statistics should just invalidate the
plan. I don't think it is worth trying to determine if they changed
sufficiently or not --- you might as we just replan.

> * Mark plan as "tried" when parameters of returned set out of statistical
> control, create alternate plan hill-climbing to statical control.
> ** Too many/too few rows relative to plan expectations
> *** Auto-sample for better statistics?
> ** History of plan shows throughput time for result set varies excessively
> (need more execution stability, possibly at expense of median optimality).

This is a new idea, that you are remembering bad plans. I am unsure how
we would track that information. It gets into the area of having the
optimizer change its behavior based on previous runs, and I am not sure
we have ever agreed to get into that kind of behavior.

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