Re: generic options for explain

2009/5/24 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Sun, May 24, 2009 at 8:44 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>> On Sun, May 24, 2009 at 12:31 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>> Hello
>>>
>>> why we develop a new syntax?
>>>
>>> we should have a secondary function explain_query(query_string,
>>> option) that returns setof some. Next function should be
>>> explain_query_xml. I thing so for typical use EXPLAIN statement is
>>> enough. And for machine procession some new function should be
>>> perfect.
>
> I don't understand - why do we want to switch from a command interface
> to a functional interface?  I was thinking:
>
> EXPLAIN (format 'xml') query...
> EXPLAIN (format 'json') query...

Personally, I thing, so this format is strange - and is some MySQLism.
There are statements like SHOW TABLES and similar. My proposal doesn't
change of current EXPLAIN. The sence is don't more complicate current
syntax and do things simply. Function has more advantage then one:

a) don't need changes in parser
b) simply add new variants
c) simply add new parameters

>
> What you're proposing will certainly be harder to code as well as more
> different from what we have now.  My approach has the advantage of
> being able to leverage the main parser to parse both the query and the
> options; with this approach, you'll need to pass the query text back
> to the main parser and then write separate code of some sort to parse
> the options.  Seems like extra work for no gain.
>
> The only possible advantage I can see of a functional interface is
> that the current implementation of EXPLAIN is treated as a utility
> command, so you can't do something like this:
>
> INSERT INTO foo (EXPLAIN SELECT 1);
>
> In my mind, however, fixing that would be preferable to (and probably
> easier than) changing the whole syntax.
>
>> I agree. We shouldn't be overloading EXPLAIN with a new option every
>> time someone thinks of something new they'd like to see. XML EXPLAIN
>> output would obviously be extensible and machine readable. We could
>> easily produce a library of XSLT stylesheets to format the output in
>> different ways without cluttering the server with extra code. The
>> benefits to applications that want to read the output are also pretty
>> obvious.
>
> Well, the major benefit of the existing EXPLAIN output is that you
> don't need an XSLT stylesheet to read it.  You can just be sitting
> there in psql and do an EXPLAIN, and look at the results, and solve
> your problem.  As we add options to EXPLAIN, I would like to see us
> support those in both the plaintext output and the XML output (and
> hopefully the JSON output) using the same syntax.
>
> If, for example, I need more information on what my hash joins are
> doing, I'd like to be able to do:
>
> EXPLAIN ('hash_detail', 'on') query...
>

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, ...)

Pavel

> ...and have it tell me the # of buckets and batches for each hash join
> and whatever other information might be useful in that context.  I
> *don't* want to have the solution to that problem be: run
> explain_xml() on your query, then install an XSLT parser, then use
> this pregenerated XSLT stylesheet to extract your data from the XML
> document you got back from step #1, then view the resulting HTML in
> your web browser.  That may be a good solution for someone, but it
> certainly doesn't sound convenient for anyone who works primarily at
> the command line.
>
> It's also worth noting that there are some kinds of statistics (for
> example, I/O statistics) which can't be gathered without significantly
> impacting the performance of the query.  So you can't just take the
> approach of turning on every possible instrumentation when XML output
> is requested.
>
> I do agree with your point that even with an extensible options
> syntax, we can't just add an option for any old  thing that someone
> wants.  We should try to come up with a somewhat comprehensive list of
> types of instrumentation that someone might want and then group them
> into categories, with one toggle per category.
>
> ...Robert
>