Re: generic options for explain

From: Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Dave Page <dpage(at)pgadmin(dot)org>, "pgsql-hackers\(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: generic options for explain
Date: 2009-05-25 10:24:59
Message-ID: 87r5yd8op0.fsf@hi-media-techno.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

After having read all the followups I already received, I prefer to
answer to this particular message.

Robert Haas <robertmhaas(at)gmail(dot)com> writes:

> On Sun, May 24, 2009 at 3:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.
[...]
> 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.

I think the summary here is to say that we want two modes of operations:
- the current one, which continues to get refinements

- a new one conveying all possible information in machine readable
formats, possibly with some tools to handle it easily: XML output and
maybe XSLT stylesheets

> 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. :-(

That's only because you seem to be thinking that having core PostgreSQL
do the first half of the work means you as a user will have to do the
second part. I assume pgadmin and phppgadmin developers will offer their
users some graphical approach to the output reading, with dynamic
filtering, eg.

I don't see anything stopping you to provide a simple way to have the
same facility into psql. You can already have the query output filtered
by any script you want this way:
=# \o |my_presentation_script <style name> | <stylesheet full path>
=# explain XML ...
=# \o

Now, we talked about a better filtering integration into psql more than
once in the past, so there's a path for you to have both complete
EXPLAIN and command line tool suite integration, I'd say.

Oh and by the way, even if I don't like XML that much, this battled is
lost already. The way I understand it, -core will accept only two
EXPLAIN outputs: fit on screen and complete. The second one will have to
be easy for machine consumption, and so use a format anyone can work
with. The choice has been made, it's XML.

If you want to develop analysis tools which will parse JSON rather than
XML, nothing stops you writing a stylesheet to provide JSON output from
XML. Of course then you want this to happen server-side, so you want the
EXPLAIN output to be processed from within the database, all the more if
it's XML and we have the ability to apply XSLT to XML content already in
the server.

But being able to apply SQL level function calls to EXPLAIN output seems
a different subject alltogether...

Regards,
--
dim

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message tomas 2009-05-25 11:05:07 Re: generic options for explain
Previous Message Pavel Stehule 2009-05-25 10:18:04 Re: proposal: SQL parser integration to PL/pgSQL