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

On Sun, May 24, 2009 at 11:57 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
>> Oops, I should have written EXPLAIN (hash_detail 'on') query... can't
>> follow my own syntax.
>>> I am sorry - this is really strange syntax . Who will use this syntax?
>>> For some parser is little bit better function call, than parametrized
>>> statement. Some dificulties with options should be fixed with named
>>> param (we are speaking about 8.5).
>>> select explain_xml("select ...", true as hash_detail, ...)
>> See to me THAT is a really strange syntax, so I guess we need some more
>> votes.
> Both of these seem both odd an unnecessary. Why not just have a setting
> called, say, explain_format which governs the output?
>
>   set explain_format = 'xml, verbose';
>   explain select * from foo;
>
> No new function or syntax would be required.

Well, then you have to issue two commands to do one thing.  I don't
much like the practice of using GUCs to control behavior that you may
only want for the duration of one statement.

The fundamental problem here, at least as it seems to me, is that we
want more options for EXPLAIN, but the current syntax won't support
it, because it requires making everything a keyword (and fixing the
order).  So we can either add enough punctuation to de-confuse the
parser (which is what I tried to do) or we can switch to a totally
different method of controlling EXPLAIN behavior (as you and Pavel are
advocating).

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!

My initial thought was to just use a list of key words for the
parameters, like this:

EXPLAIN (XML, VERBOSE) query...

...but I decided that wasn't a great idea, because it means that every
parameter has to be a boolean, which is probably more limiting than we
want to be.  It also gets confusing because some parameters (like
"XML" and "JSON") are incompatible while others (like "HASH_DETAIL"
and "MERGE_DETAIL") can be used together, but there's not a lot of
clue in the syntax itself that this is the case.  On the other hand,
if you write:

EXPLAIN (FORMAT 'XML', FORMAT 'JSON') ...
EXPLAIN (HASH_DETAIL 'ON', MERGE_DETAIL 'ON') ...

...it's obvious that the first case is qualitatively different from
the second one.  I think this makes it easier to code the behavior in
a way that is consistent and correct, and also easier to document for
the benefit of our users.  Now, we can do this part of it even if we
ultimately end up with a different overall syntax, for example:

explain_query(...query..., 'xml' as format, true as hash_detail)

I do suspect this will end up being a more complicated patch, and as I
said before, I don't really see the point.

As a point of historical interest, way back when (early 1997), we used
to support this syntax:

EXPLAIN WITH [COST|PLAN|FULL] query...

...but of course that's now impossible because WITH can be the first
word of a select query.

...Robert