Re: generic options for explain

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.

regards, tom lane