Re: Automatic function replanning

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
changessufficiently (per planner parameter)** when new ANALYZE statistics are available and per planner parameter
differ
 
sufficiently from prior statistics.

* 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).       


> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly