Re: generic options for explain - Mailing list pgsql-hackers

On Tue, May 26, 2009 at 3:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, May 26, 2009 at 3:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> These are implementation details ;-).  Let's get a definition that
>>> everyone can sign off on, and then worry about what has to be done
>>> to the code to make it happen.
>
>> I'm actually not sure there's a whole lot to hash out... I was going
>> to take a crack at writing some code.
>
> 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.  Show us a spec for the output
> format, then think about code.  (This was somewhere around slide ten
> here:  http://momjian.us/main/writings/pgsql/patch.pdf  ;-))

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.

At any rate, that email might not be as detailed as what you're
looking for, but it's certainly a start.  I don't really know how the
table-format output is going to work out; I have to look at the code
more to get a feeling for that.  But I think with respect to XML or
JSON, there really aren't too many options for how it can look, modulo
minor syntax tweaks like arguing about whether the join type should be
labelled "JoinType" or "jointype" or "join_type".  Still, if you have
comments or think I'm overlooking something important, I definitely
would like to know about that now before I put more time into it.

I recognize that we haven't come to a consensus on the best possible
syntax for EXPLAIN options, but it seems to me that threshold issue
for improving EXPLAIN is everyone agreeing that we're going to allow
for some kind of extendable syntax that doesn't rely on all options
being keywords (presented in a fixed order, no less!).  You caved in
on that point upthread and I don't think we have any other holdouts.
Now, of course, my syntax is the best possible one in the entire
universe, but if by chance there is a technically feasible alternative
syntax on which more than one person can agree (note: this has not
happened yet), adjusting my patch to use that syntax rather than the
one I stole from Peter shouldn't be too hard.

A second issue on which we don't have consensus is a method to capture
explain output.  I am 100% of the opinion that there are only two
sensible things to do here: (1) make EXPLAIN a fully reserved keyword
so that we can use it just like a SELECT, or (2) provide a built-in
function like pg_explain() that calls EXPLAIN with a user-specified
set of arguments, and which third-party tools can count on to be
installed.  Since you labelled (1) as a non-starter and AFAICS you're
the only holdout on making (2) a built-in rather than something
everyone has to define for themselves, I'm hopeful that we'll bring
you around.  :-)

The final issue on which we don't have a clear consensus is what OTHER
new options we want for EXPLAIN aside from choice of output format.  I
posted a few ideas that I have and solicited some others upthread, but
I think that the volume of email on other aspects of this patch has
deprived people of the necessary time and space to think about how
they might like to use an extensible options syntax once we have it -
not to mention that the original patch was only posted 3 days ago and
on a day when many of us were on airplanes, about to get on airplanes,
or still jet-lagged.  Personally, I think that that's the most
interesting aspect of this whole project so I hope it gets some
attention going forward, but I'm not too concerned about the exact
timing of that attention.  The point is that people not-infrequently
come up with more stuff they'd like to see in EXPLAIN output, and
those ideas get shot down because we don't have the syntax.  If we fix
the syntax, those ideas will come back around again in due course, and
we'll be able to consider them on their merits rather than
peremptorily shooting them down.

...Robert