If I may..
this answer looks more "philosophical" than "practical".
On Oracle (maybe 10, I don't remember well) was introduced the possibility to explicitly store an execution plan, so that a given query use THAT plan ie. dont go thru planner job.
OK if someone do stupid things, one may get stupid results...it was an "expert only" functionality :-)
Still, in some cases, it was very useful to manage the rare cases where the planner cannot, for whatever reason do a good job.
OK its not the way postgres do behave. Still, in some cases...
On Thu, Feb 16, 2023 at 5:08 PM David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
On Thu, Feb 16, 2023 at 8:48 AM cen <cen.is.imba@xxxxxxxxx> wrote:
- does the planner take previous runs of the same query and it's
execution time into account? If not, why?No, because that isn't how it works. And while I'm no planner expert I'm not imagining any particularly compelling argument for why it would even make sense to try. The book-keeping would be expensive and dealing with supposedly an ever-changing dataset would in many cases make any such comparisons be meaningless.
- assuming the query to be immutable, would it be possible for the
planner to microbenchmark a few different plans instead of trying to
estimate the cost?
As in, actually executing the query with different plans and caching the
best one.No, the planner may not cause execution. While I could imagine extending EXPLAIN to somehow retrieve and maybe even try alternative plans that have been fully constructed today I'm not holding my breath.There is little reason for the project to give any real weight to "assuming the query to be immutable". We do want to fix the planner to behave better if it is mis-behaving, otherwise you do have access to cost parameters, and potentially other planner toggles if you've truly run into an intractable problem.David J.
