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

David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> 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.

There's a number of problems in this area, but I think the really
fundamental issue is that for speed reasons the planner wants to
reject losing plan alternatives as quickly as possible. So we simply
don't pursue those alternatives far enough to produce anything that
could serve as input for EXPLAIN (in its current form, anyway).
What that means is that a trace of add_path decisions just can't be
very useful to an end user: there isn't enough data to present the
decisions in a recognizable form, besides which there is too much
noise because most of the rejected options are in fact silly.
So indeed we find that even hard-core developers aren't interested
in consuming the data in that form.

Another issue is that frequently the problem is that we never
considered the desired plan at all, so that even if you had a
perfectly readable add_path trace it wouldn't show you what you want
to see. This might happen because the planner is simply incapable of
producing that plan shape from the given query, but often it happens
for reasons like "function F() used in the query is marked volatile,
so we didn't flatten a subquery or consider an indexscan or whatever".
I'm not sure how we could produce output that would help people
discover that kind of problem ... but I am sure that an add_path
trace won't do it.

So, not only am I pretty down on exposing OPTIMIZER_DEBUG in
its current form, but I don't really believe that adding hooks
to add_path would allow an extension to produce anything of value.
I'd for sure want to see a convincing demonstration to the contrary
before we slow down that hot code path by adding hook calls.

regards, tom lane