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