Re: generic options for explain

On Tue, May 26, 2009 at 5:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Tue, May 26, 2009 at 3:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I still haven't seen anything but formless handwaving as far as the "SQL
>>> table" output format goes.  For that matter, there's not much more than
>>> handwaving behind the "XML" meme either.
>
>> OK, how about this:
>> http://archives.postgresql.org/message-id/603c8f070905241827g74c8bf9cie9d98e38037a8356@mail.gmail.com
>
>> I note in passing that there have been 51 messages posted to this
>> thread since I wrote that email, and none of the were responses to it.
>
> Well, we were having too much fun arguing about trivia ;-).  And I
> suspect a number of people were too jet-lagged to keep track of what
> they'd read and what not.  Anyway, good, we have a starting point.
>
> Some issues that I see here:
>
> 1.  You seem to be assuming that each table row will represent exactly
> one plan node, no more, no less.  That's okay as a first approximation
> but it breaks down on closer examination.  In particular, where will you
> hang the information that's already available about trigger execution
> costs?  Those are not associated with any particular plan node, as they
> occur atop the whole plan.  The same goes for the total execution time
> of course, and I can foresee other types of stats that we might gather
> someday that would be hard to tie to any specific plan node.
>
> In XML this is soluble by having a toplevel node <ExplainResults> that
> contains not only the plan tree but other children.  I'm not seeing how
> to translate that into a SQL table, though.  Or at least not just one
> SQL table.
>
> 2.  You didn't say anything about how any but simple scalar fields will
> be represented.  Filter conditions and sort keys are particularly
> interesting here.  I'm not really happy with just plopping down the same
> textual output we have now --- that is just as human-friendly-and-not-
> machine-friendly as before, only with a slightly smaller scope.  I can
> foresee for example that someone might wish to extract the second or
> third sort key expression from a Sort node's sort key list.  Or what
> about problems such as "find which nodes this field is used in"?
>
> 3.  You left us with a handwave about how the tree structure will be
> represented in a table.  Needs to be explicit.  And it's not just
> simple child relationships that should be represented ... tell us
> about initplans and subplans, too.
>
> 4.  The point about having lots of NULL columns is an annoyance that
> could escalate to the point of near unusability.  To get a feeling for
> how workable that is, we need a pretty exact list of the set of output
> columns, not just a rough list of the kinds of things that will be
> there.

Responding to these in bulk, I think that 1, 3, and 4 are pretty
convincing arguments that the SQL-based output format is
underspecified. I hereby promise not to do anything about that
without further discussion, which is an easy promise to make
considering that in light of those comments I have no idea what it
should look like. I think (1) is the most damning point. However, as
far as I can see, none of these will affect XML or JSON.

With respect to (2), I think we should output the same text format
that we have now, for starters. I agree that's not the only thing
that someone might want, but I think there's a pretty good argument
that it's ONE thing that someone might reasonably want, depending on
the application. If someone cares to build a better mousetrap in this
area, it can be added on once we figure out the design, and without
breaking anything! - that's sort of the whole point of this exercise.

...Robert