As an end user that spends a lot of time optimizing pretty complicated queries, I'd say that something like this could be useful.
Right now the optimizer is mostly a black box. Why it chooses one plan or the other, it's a mystery. I have some general ideas about that,
and I can even read and sometimes debug optimizer's code to dig deeper (although it's not always possible to reproduce the same behavior as in the production system anyway).
I'm mostly interested to find where exactly the optimizer was wrong and what would be the best way to fix it. Currently Postgres is not doing a great job in that department.
EXPLAIN output can tell you about mispredictions, but the logic of choosing particular plans is still obscure, because the reasons for optimizer's decisions are not visible.
If configuring OPTIMIZER_DEBUG through GUC can help with that, I think it would be a useful addition.
Now, that's general considerations, I'm not somebody who actually uses OPTIMIZER_DEBUG regularly (but maybe I would if it's accessible through GUC),
I'm just saying that is an area where improvements would be very much welcomed.
-Vladimir
On Tue, 3 Jan 2023 at 19:59, Ankit Kumar Pandey <itsankitkp@gmail.com> wrote:
>
>
> On 03/01/23 08:38, David Rowley wrote:
> > Do you actually have a need for this or are you just trying to tick
> > off some TODO items?
> >
> I would say Iatter but reason I picked it up was more on side of
> learning optimizer better.
I think it's better you leave this then. I think if someone comes
along and demonstrates the feature's usefulness and can sell us having
it so we can easily enable it by GUC then maybe that's the time to
consider it. I don't think ticking off a TODO item is reason enough.
> Also from the thread,
>
> https://www.postgresql.org/message-id/20120821.121611.501104647612634419.t-ishii@sraoss.co.jp
>
> > +1. It would also be popular with our academic users.
> >
> There could be potential for this as well.
I think the argument is best coming from someone who'll actually use it.
David