Re: [PATCH] Improve ability to display optimizer analysis using OPTIMIZER_DEBUG

On Wed, 4 Jan 2023 at 17:39, Vladimir Churyukin <vladimir(at)churyukin(dot)com> wrote:
>
> On Tue, Jan 3, 2023 at 7:41 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>> From what I can see here, the motivation to make this a useful feature
>> is backwards from what is normal. I think if you're keen to see a
>> feature that allows you better visibility into rejected paths then you
>> need to prove this is it rather than speculating that it might be
>> useful.
>>
>
> You can't see people using the feature unless you make it useful. If it's not useful right now (because it's implemented as a compile-time flag with stdout prints for example),
> it doesn't mean it's not useful when it becomes more convenient. Probably the best way to find out is to create a *convenient* extension and see if people start using it.

I don't think anyone is against making it useful. It's just not
seemingly useful enough for either Tom or I to make use of it. Nobody
else seems to have come along to tell us that it's useful to them.
Some people only speculated that it might be useful.

As I said before, if someone wants to make this work, then I think the
problem needs to be approached from the opposite direction. i.e they
have a plan that they're not happy with and they need to come up with
something that usefully shows the reason why the plan that they expect
to be better is not chosen. That's not what's happened here. add_path
does not just reject paths based on cost, so ISTM, for this to be
meaningful, add_path would need to do something to specify the reason
that the path was rejected, i.e a similarly costed path has pathkeys,
this one does not.

> I agree, extended EXPLAIN output would be a much better solution than writing into stdout. Can be implemented as an extra EXPLAIN flag, something like EXPLAIN (TRACE).
> One of the issues here is the result will rather be pretty long (and may consist of multiple parts, so something like returning multiple refcursors might be necessary, so a client can fetch multiple result sets.
> Otherwise it won't be human-readable. Although it's not necessary the purpose, if the purpose is to make it machine-readable and create tools to interpret the results, json format and a single resultset would be ok.
> The result can be represented as a list of trace events that shows profiler logic (the traces can be generated by the hook you mentioned and or by some other additional hooks).
> Is that what you were talking about?

The thing I had in mind was some mode that would record additional
details during planning that could be tagged onto the final plan in
createplan.c so that EXPLAIN could display them. I just think that
EXPLAIN is the place where people go to learn about _what_ the plan is
and it might also be the place where they might expect to go to find
out more details about _why_ that plan was chosen. I by no means have
a fully bakes idea on what that would look like, but I just think that
dumping a bunch of lines to stdout is not going to be useful to many
people and we need to think of something better in order to properly
make this useful.

> Another thing, since people react to this TODO item on https://wiki.postgresql.org/wiki/Todo, maybe it's better to modify or remove it, so they don't spend time working on something that is pretty much a dead end currently?

I've just adjusted it based on the discussion that's going on on this thread.

David