Re: machine-readable explain output v4 - Mailing list pgsql-hackers

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