db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendle...@amberpoint.com>
Subject Re: logQueryPlan readability
Date Tue, 03 Oct 2006 22:33:03 GMT
>> 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



Mime
View raw message