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... Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Thu, Feb 16, 2023 at 5:08 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Feb 16, 2023 at 8:48 AM cen <cen.is.i...@gmail.com> 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. > >
