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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
