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

On Sun, May 24, 2009 at 3:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> I wouldn't mind having a GUC to set the *default* explain behavior -
>> but I'd still like to be able to override it for a particular command
>> if I so choose.  And that's not going to be possible with your syntax:
>> if explain_format is set to 'xml, verbose' and I want plain text
>> output for one command, how do I get it?  Presumably I have to change
>> explain_format, run my EXPLAIN, and then change it back again.  Blech!
>
> You know about SET LOCAL, no?  I don't think this argument is very
> convincing.

I completely fail to see how that helps me.  It's not faster or fewer
commands to type:

begin transaction; set local whatever; explain blah; commit transaction;

than it is to type:

set whatever; explain blah; set oldwhatever;

> On the other side of the coin, I'm strongly against inventing more than
> one new output format for EXPLAIN, and so any argument that depends on
> examples such as "xml vs json" is falling on deaf ears here.  I think
> that we only need an XML option, and so EXPLAIN ANALYZE XML ... doesn't
> seem untenable.  What other options than those do you really need?
> Not ones to add or remove output fields; we'd expect the client to
> ignore fields it doesn't care about.

It's not just about me; we've had many requests for new EXPLAIN features.

Personally, I'd like to see the number of buckets and batches that a
hash join uses (predicted and actual), and maybe (with ANALYZE) the
number of hash collisions.  I'd like to see memory utilization
statistics (peak memory utilization of hash join, for example).  Other
people have requested I/O statistics (which you objected to on the
grounds that it would be too much overhead, so clearly if we're ever
going to do it it will have to be optional), and most recently number
of tuples discarded by the filter condition.  We've also had requests
to suppress some information (like costs) for planner regression
testing.

I really don't see the point in restricting the syntax of EXPLAIN in
this way.  I don't know exactly what sorts of useful options people
will come up with, but I do know that as long as we have an extremely
limiting options syntax, they can all be shot down on the grounds that
including them in the default output is too cumbersome for regular use
(which is absolutely true).  On the other hand, I think it's
incredibly naive to suppose that EXPLAIN XML is going to make anyone
very happy.  There are only two ways this can work out:

1. We'll make EXPLAIN XML output everything and the kitchen sink.  In
this case, we'll soon get complaints along the lines of: "I can't use
regular EXPLAIN because it doesn't include the field that I need, but
the output of EXPLAIN XML is so voluminous that I can't read through
it by hand."
-or-
2. We'll be very restrictive about adding fields to EXPLAIN XML just
as we are now for plain EXPLAIN, in which case we haven't solved
anything.

I think XML output format is a complete distraction from the real
issue here, which is that there are certain pieces of information that
are sometimes useful but are not useful enough to justify including
them in the EXPLAIN output 100% of the time.  By just punting all that
stuff to EXPLAIN XML, we're just saying that we're not interested in
creating a workable set of options to allow users to pick and choose
the information they care about - so instead we're going to dump a
huge chunk of unreadable XML and then make it the user's problem to
find a tool that will extract the details that they care about.  Boo,
hiss.

Anyway, I'm suprised by the reaction to this patch, but I'll drop it.
I would like to make the EXPLAIN syntax more powerful for command-line
use, and I'd implement XML format and JSON along the way just for
completeness.  But I don't have much interest in creating an XML
output format that is the ONLY way of getting more information,
because I'm a command-line user and it does me no good at all.  :-(

...Robert