Re: machine-readable explain output v4

On Wed, Aug 12, 2009 at 9:42 AM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
> 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.

auto_explain sort of hooks into the middle of the main EXPLAIN
machinery in a strange way. It's not comparable to a real EXPLAIN
ANALYZE because, for example, it won't report_triggers(). But we
could still put a container around it of some sort by modifying
contrib/auto_explain. Perhaps you'd care to propose a patch?

...Robert