Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?

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(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Thu, Feb 16, 2023 at 8:48 AM cen <cen(dot)is(dot)imba(at)gmail(dot)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.
>
>