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 changes sufficiently (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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly