One place I struggle is that I find logged query plans to be rather
intimidating and hard to read.
I liked the ideas about emitting the information in XML format and then
building a tool to analyze it.
It's kind of a big first step, though.
I was thinking that there might be some smaller, easier, things that we
could do which would offer some smaller wins, but might still be worth it.
Ideas that I had include:
1) logQueryPlan output intermixes timing information ("we processed this
many rows in this many milliseconds") with structural information
("we did a distinct scalar aggregate of a sort of a union"). I was
thinking that we could refine the logQueryPlan behavior so that you
could say things like:
logQueryPlan=[nodeTree | estTime | actTime | estRows | actRows]
so that the user could choose a subset of the information if they were
only interested in the "shape" of the tree, say, and not the detailed
performance numbers; the idea is just that we give the user a way to
pick a smaller subset of the information to give them a more approachable
amount of output.
2) The indentation for the qualification information seems to get lost,
in my experience, making the display hard to read. Also, since "real"
query plans are often quite deep, I wonder whether we should display
things with a fixed 1-or-2 space indentation "step" rather than using
tabs. The advantage of emitting hard tabs is that the user can reset
their tab stops, but if they don't, the lines almost always wrap or
get truncated. Avoiding line wrap could also take the form of slightly
less wordy displays, so that instead of
optimizer estimated row count: 1439201.17
we could say
est rows: 1439201
so that as the indentation started to grow, we'd still not wrap lines.
Of course, this trades off line-wrapping for less-self-evident output.
3) There's a bunch of kind of detailed internal information in the query
plan output, and in particular for many common queries we output a lot
of information about settings which the user probably didn't set, but
which are just set to their default value. I'm thinking about things
like "Ordered nulls: false" and "Unknown return value: false" and the like.
I wonder whether we could change the various output routines so that they
only emitted information like this if it was set to a non-default value,
which seems like in the common case it might make the query plan display
substantially shorter. If we didn't want to do this unconditionally, I
thought that maybe we might give the user a knob like:
logQueryPlanFormat=[brief | full]
Do people think that things like this would be worth doing? Or would it be
better just to bite the bullet and pursue the full-on "XML format and a
separate analysis tool" proposals?
thanks,
bryan
