Re: machine-readable explain output v4

Csaba Nagy wrote:
> On Tue, 2009-08-11 at 23:58 +0200, Andrew Dunstan wrote:
>
>> Well, I don't think that the fact that we are producing machine-readable
>> output means we can just ignore the human side of it. It is more than
>> likely that such output will be read by both machines and humans.
>> Obviously, we need to make sure that it meets machine processing needs
>> first, but once we have done that we should not ignore the human
>> requirements.
>>
>
> XML is easy to transform to about anything else. I would vote for the
> XML output to only focus on machine readability and completeness, and
> then if needed provide style sheets to make it human readable. Then
> anybody could have his preferred style to look at it. If there would be
> a tool to format the XML according to some style sheet (to make it easy
> for those who don't care about XML and style sheets), I would volunteer
> to provide the XSL style sheets for different formats on request.
>
>
>

Have you actually looked at a logfile with this in it? A simple
stylesheet won't do at all. What you get is not an XML document but a
text document with little bits of XML embedded in it. So you would need
a program to parse that file and either turn it into a single legal XML
document or pass each piece of XML individually to your XSLT processor.
Bleah.

And all this because you pose a false dichotomy between correctness and
completeness on one hand and human readability on the other. I don't
accept that at all. I think we can and should improve human readability
without sacrificing anything on the correctness and completeness front.
In fact, that also needs improving, and we can do them both at the same
time.

I want to be able to have machine readable explain output, but I also
want to be able to browse the logs without wasting more brain cells than
necessary and without having to use external tools other than by
standard text browser (less). As Pooh Bear said, "Both please!"

One thing I have noticed that we should talk about is that the explain
XML output doesn't contain the query that is being explained. That's
unfortunate - it means that any logfile processor will need to extract
the statement from the surrounding text rather than just pulling out the
XML and passing it to an XML processor.

Another design issue is this: The root node of an XML document is
ideally a distinguished element that can't occur within itself.
auto-explain doesn't seem to be doing this. It outputs fragments that
have <Plan> as the root element rather than <explain>

Here's a tiny fragment of my logfile with auto_explain.log_min_duration
= 0 and auto_explain.log_format = 'xml' settings:

STATEMENT: SELECT 1 AS one;
LOG: duration: 0.008 ms plan:
<Plan>
<Node-Type>Result</Node-Type>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>0.01</Total-Cost>
<Plan-Rows>1</Plan-Rows>
<Plan-Width>0</Plan-Width>
</Plan>

But a <Plan> node can appear as the decendant of a <Plan> node, so this
violates the design principle I enunciated above.

(Anecdote: The very first use I even made of Postgres was at the end of
2002, for an Application I designed called "30 minute App" which let
people design online and generate a questionnaire application for small
devices (Palm, Blackberry, PocketPC) and stored the application metadata
as a piece of XML in a text field in Postgres.)

cheers

andrew