Re: explain root element for auto-explain

The attached tiny patch sets the <explain> root element for auto-explain
XML output, so it looks something like this:

<explain xmlns="http://www.postgresql.org/2009/explain">
<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>
</explain>

The JSON output looks like this:

[
"Plan": {
"Node Type": "Result",
"Startup Cost": 0.00,
"Total Cost": 0.01,
"Plan Rows": 1,
"Plan Width": 0
}
]

This is worth doing in itself in the XML case for reasons previously
explained, but it also makes it relatively easy to add a Query-Text node
or some such to the structured output, which is very much worth having,
and would be my next proposed step.

cheers

andrew

Andrew Dunstan <andrew(dot)dunstan(at)pgexperts(dot)com> writes:
> The attached tiny patch sets the <explain> root element for auto-explain
> XML output, so it looks something like this:

This looks reasonable in itself, but it sort of begs the question on
two other things:

* what's the xmlns URL really going to be?

* what's the <Query> element in regular XML output good for? And
shouldn't we make both explain and auto-explain either have that or not?

> The JSON output looks like this:

> [
> "Plan": {
> "Node Type": "Result",
> "Startup Cost": 0.00,
> "Total Cost": 0.01,
> "Plan Rows": 1,
> "Plan Width": 0
> }
> ]

<squint> Bearing in mind that I know roughly nothing of JSON ... surely
the above is syntactically incorrect? A labeled value should be within
{...} not [...]. I think this is closely related to the point about
<Query>, ie the same semantic nesting level is missing in both cases.

regards, tom lane

Tom Lane wrote:
> Andrew Dunstan <andrew(dot)dunstan(at)pgexperts(dot)com> writes:
>
>> The attached tiny patch sets the <explain> root element for auto-explain
>> XML output, so it looks something like this:
>>
>
> This looks reasonable in itself, but it sort of begs the question on
> two other things:
>
> * what's the xmlns URL really going to be?
>

By convention it refers to a place where you publish the schema for the
document type, but it is in fact completely arbitrary, and can refer to
a non-existant resource - as long as it is unique - it's just a
namespace designator, and from an XML POV has no more semantic
significance that a schema name does in SQL.

> * what's the <Query> element in regular XML output good for? And
> shouldn't we make both explain and auto-explain either have that or not?
>
>

and also, why isn't it present in the JSON output for either? We seem to
have several places when we output an XML tag but not a corresponding
named JSON node. Is that really a good idea?

>> The JSON output looks like this:
>>
>
>
>> [
>> "Plan": {
>> "Node Type": "Result",
>> "Startup Cost": 0.00,
>> "Total Cost": 0.01,
>> "Plan Rows": 1,
>> "Plan Width": 0
>> }
>> ]
>>
>
> <squint> Bearing in mind that I know roughly nothing of JSON ... surely
> the above is syntactically incorrect? A labeled value should be within
> {...} not [...]. I think this is closely related to the point about
> <Query>, ie the same semantic nesting level is missing in both cases.
>

Looks like it. <http://www.jsonlint.com/> is useful for checking such
things.

Of course, the current JSON output from auto-explain (i.e. without the
enclosing [ ] ) is also illegal, unlike the output from "explain (format
json) select 1", which encloses the Plan node in { } inside the [ ],

cheers

andrew

Andrew Dunstan wrote:
>
>
> Tom Lane wrote:
>>
>>
>> * what's the xmlns URL really going to be?
>>
>
> By convention it refers to a place where you publish the schema for
> the document type, but it is in fact completely arbitrary, and can
> refer to a non-existant resource - as long as it is unique - it's just
> a namespace designator, and from an XML POV has no more semantic
> significance that a schema name does in SQL.
>
>

One thing I definitely think we should do is to put the namespace URL in
a header file. Think of it as being a bit like the catversion.
Hardcoding it in explain.c doesn't seem like a good idea.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> One thing I definitely think we should do is to put the namespace URL in
> a header file. Think of it as being a bit like the catversion.
> Hardcoding it in explain.c doesn't seem like a good idea.

Well, it could at least be a #define, but what's the point of exposing
it in a header file --- what other code will use that file?

regards, tom lane

Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>> One thing I definitely think we should do is to put the namespace URL in
>> a header file. Think of it as being a bit like the catversion.
>> Hardcoding it in explain.c doesn't seem like a good idea.
>>
>
> Well, it could at least be a #define, but what's the point of exposing
> it in a header file --- what other code will use that file?
>
>
>

Good point. None right now I guess. But certainly a #define.

cheers

andrew

On Tue, Aug 18, 2009 at 02:11, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>
> Tom Lane wrote:
>>
>> Andrew Dunstan <andrew(dot)dunstan(at)pgexperts(dot)com> writes:
>>
>>>
>>> The attached tiny patch sets the <explain> root element for auto-explain
>>> XML output, so it looks something like this:
>>>
>>
>> This looks reasonable in itself, but it sort of begs the question on
>> two other things:
>>
>> * what's the xmlns URL really going to be?
>>
>
> By convention it refers to a place where you publish the schema for the
> document type, but it is in fact completely arbitrary, and can refer to a
> non-existant resource - as long as it is unique - it's just a namespace
> designator, and from an XML POV has no more semantic significance that a
> schema name does in SQL.

I'd suggest using a different namespace than www.postgresql.org, just
to be sure it won't conflict with some system we use in the future.
Perhaps http://schemas.postgresql.org/<whatever>? It doesn't actually
need to exist until we want to put anything there, but it mustn't
conflict with anything else.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Are we going to publish an XML DTD for EXPLAIN, or have we already?

---------------------------------------------------------------------------

Andrew Dunstan wrote:
>
> The attached tiny patch sets the <explain> root element for auto-explain
> XML output, so it looks something like this:
>
> <explain xmlns="http://www.postgresql.org/2009/explain">
> <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>
> </explain>
>
> The JSON output looks like this:
>
> [
> "Plan": {
> "Node Type": "Result",
> "Startup Cost": 0.00,
> "Total Cost": 0.01,
> "Plan Rows": 1,
> "Plan Width": 0
> }
> ]
>
> This is worth doing in itself in the XML case for reasons previously
> explained, but it also makes it relatively easy to add a Query-Text node
> or some such to the structured output, which is very much worth having,
> and would be my next proposed step.
>
> cheers
>
> andrew

>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Are we going to publish an XML DTD for EXPLAIN, or have we already?

Not a DTD, but I am working on an XML Schema (DTDs are a bit yesterday).

cheers

andrew

Andrew Dunstan wrote:
> Bruce Momjian wrote:
> > Are we going to publish an XML DTD for EXPLAIN, or have we already?
>
> Not a DTD, but I am working on an XML Schema (DTDs are a bit yesterday).

OK, either one would be good.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Bruce Momjian wrote:
>> Are we going to publish an XML DTD for EXPLAIN, or have we already?

> Not a DTD, but I am working on an XML Schema (DTDs are a bit yesterday).

+1 ... I asked for a spec for the output format before, and this would
do fine.

regards, tom lane

Andrew Dunstan wrote:
> Bruce Momjian wrote:
>> Are we going to publish an XML DTD for EXPLAIN, or have we already?
>
> Not a DTD, but I am working on an XML Schema (DTDs are a bit yesterday).
>
>

Here is a RelaxNG spec which people might find a bit easier to read. It
has been autocreated by a little tool called trang, that I used on a
very large body of explain output that I produced by mangling the
regression tests (and, incidentally, crashing the server in the result -
I still have to chase that up).

I have a couple of questions, however. First, in that long list of
alternatives for a Plan node, can any of them occur more than once?
Second, we are using Item as a child of both Output and Sort-Key nodes.
Are they really describing the same thing? And in any case, Item is a
wonderfully non-informative name, as is Output, for that matter.

BTW - I know this requires tweaking - those xsd:NCName values will
probably just become text, for example.

cheers

andrew

default namespace = "http://www.postgresql.org/2009/explain"

start =
element explain {
element Query {
Plan,
element Triggers { empty },
element Total-Runtime { xsd:decimal }
}
}
Plan =
element Plan {
(element Actual-Loops { xsd:integer }
| element Actual-Rows { xsd:integer }
| element Actual-Startup-Time { xsd:decimal }
| element Actual-Total-Time { xsd:decimal }
| element Alias { text }
| element Filter { text }
| element Function-Name { xsd:NCName }
| element Hash-Cond { text }
| element Index-Name { xsd:NCName }
| element Join-Filter { text }
| element Join-Type { xsd:NCName }
| element Merge-Cond { text }
| element Node-Type { text }
| element One-Time-Filter { text }
| element Output { Item+ }
| element Parent-Relationship { xsd:NCName }
| element Plan-Rows { xsd:integer }
| element Plan-Width { xsd:integer }
| element Plans { Plan* }
| element Recheck-Cond { text }
| element Relation-Name { xsd:NCName }
| element Scan-Direction { xsd:NCName }
| element Schema { xsd:NCName }
| element Sort-Key { Item+ }
| element Sort-Method { text }
| element Sort-Space-Type { xsd:NCName }
| element Sort-Space-Used { xsd:integer }
| element Startup-Cost { xsd:decimal }
| element Strategy { xsd:NCName }
| element Subplan-Name { text }
| element Total-Cost { xsd:decimal })*,
element Index-Cond { text }?
}
Item = element Item { text }

On Thu, Aug 20, 2009 at 9:30 AM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>
>
> Andrew Dunstan wrote:
>>
>> Bruce Momjian wrote:
>>>
>>> Are we going to publish an XML DTD for EXPLAIN, or have we already?
>>
>> Not a DTD, but I am working on an XML Schema (DTDs are a bit yesterday).
>>
>>
>
> Here is a RelaxNG spec which people might find a bit easier to read. It has
> been autocreated by a little tool called trang, that I used on a very large
> body of explain output that I produced by mangling the regression tests

This is definitely easy to read, especially for XML.

> (and, incidentally, crashing the server in the result - I still have to
> chase that up).

Hmm.

> I have a couple of questions, however. First, in that long list of
> alternatives for a Plan node, can any of them occur more than once?

I don't think so. But I also don't think Index-Cond should be treated
specially, as you have done here.

> Second,
> we are using Item as a child of both Output and Sort-Key nodes. Are they
> really describing the same thing? And in any case, Item is a wonderfully
> non-informative name, as is Output, for that matter.

Well, I can't help Output. That's what 8.4-EXPLAIN calls it. I do
think maybe it should be ripped out of EXPLAIN (VERBOSE) and made a
separate option.

Are they really the same thing? Obviously not. I just needed a way
to make a list of scalars in XML and I picked that for want of
creativity.

> BTW - I know this requires tweaking - those xsd:NCName values will probably
> just become text, for example.

As far as I'm concerned, you're already way ahead producing something
that fits on the screen.

...Robert

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> I have a couple of questions, however. First, in that long list of
> alternatives for a Plan node, can any of them occur more than once?

No.

> Second, we are using Item as a child of both Output and Sort-Key nodes.
> Are they really describing the same thing? And in any case, Item is a
> wonderfully non-informative name, as is Output, for that matter.

They are both describing expressions. I wanted to rename Item as Expr,
if you'll recall. But I think we should have a concrete plan about
all the tweaks we want to make to the output schema before doing
anything, so I haven't pushed to change it immediately.

I don't see anything wrong with Output --- what else would you call the
output expressions of a node?

regards, tom lane

I wrote:
>
>
> Andrew Dunstan wrote:
>> Bruce Momjian wrote:
>>> Are we going to publish an XML DTD for EXPLAIN, or have we already?
>>
>> Not a DTD, but I am working on an XML Schema (DTDs are a bit yesterday).
>>
>>
>
> Here is a RelaxNG spec which people might find a bit easier to read.
> It has been autocreated by a little tool called trang, that I used on
> a very large body of explain output that I produced by mangling the
> regression tests (and, incidentally, crashing the server in the result
> - I still have to chase that up).

Updated version with more complete information (regression crash was due
to my bad script).

cheers

andrew

default namespace = "http://www.postgresql.org/2009/explain"

start =
element explain {
element Query {
Plan,
Triggers,
element Total-Runtime { xsd:decimal }
}+
}
Plan =
element Plan {
(element Actual-Loops { xsd:integer }
| element Actual-Rows { xsd:integer }
| element Actual-Startup-Time { xsd:decimal }
| element Actual-Total-Time { xsd:decimal }
| element Alias { text }
| element CTE-Name { text }
| element Command { text }
| element Filter { text }
| element Function-Name { text }
| element Hash-Cond { text }
| element Index-Cond { text }
| element Index-Name { text }
| element Join-Filter { text }
| element Join-Type { text }
| element Merge-Cond { text }
| element Node-Type { text }
| element One-Time-Filter { text }
| element Output { Item* }
| element Parent-Relationship { text }
| element Plan-Rows { xsd:integer }
| element Plan-Width { xsd:integer }
| element Plans { Plan* }
| element Recheck-Cond { text }
| element Relation-Name { text }
| element Scan-Direction { text }
| element Schema { text }
| element Sort-Key { Item+ }
| element Sort-Method { text }
| element Sort-Space-Type { text }
| element Sort-Space-Used { xsd:integer }
| element Startup-Cost { xsd:decimal }
| element Strategy { text }
| element Subplan-Name { text }
| element Total-Cost { xsd:decimal },
| element TID-Cond { text }
)*
}
Triggers =
element Triggers {
element Trigger {
element Trigger-Name { text },
element Constraint-Name { text }?,
element Relation { text },
element Time { xsd:decimal },
element Calls { xsd:integer }
}*
}
Item = element Item { text }

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Updated version with more complete information (regression crash was due
> to my bad script).

I took a look through the source code to match it against this. I found
that you missed a couple of possibilities: we have <Notify /> and
<Utility-Statement /> as alternatives to <Query> just below <explain>.

Also, it looks to me like <Item> is simply being used as an element
of lists (cf ExplainPropertyList); I was mistaken to equate it with
<Expr>. I don't know XML well enough to understand if we really need
that syntactic detail, or if there's a more idiomatic way to treat
lists.

BTW, I wonder why <explain> doesn't have an init-cap like every other
node type name ...

regards, tom lane

Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>> Updated version with more complete information (regression crash was due
>> to my bad script).
>>
>
> I took a look through the source code to match it against this. I found
> that you missed a couple of possibilities: we have <Notify /> and
> <Utility-Statement /> as alternatives to <Query> just below <explain>.
>

What causes those to happen? Here's how I mangled the regression tests
to produce the output that this analysis was taken from:

perl -spi.bak -e
's/^(insert|update|select|delete|declare|execute|create table .* as)
/explain (analyse true, verbose true, format xml) $1 /i;' *.sql

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Tom Lane wrote:
>> I took a look through the source code to match it against this. I found
>> that you missed a couple of possibilities: we have <Notify /> and
>> <Utility-Statement /> as alternatives to <Query> just below <explain>.

> What causes those to happen?

You can get a <Notify> via explaining a command that's been affected
by a rule like
CREATE RULE foo ... DO ALSO NOTIFY foo;

I think the <Utility-Statement> case is not actually reachable code at
present. NOTIFY is the only utility command that's allowed in CREATE
RULE, and auto-explain is hooked in in a place where it can't see
utility statements at all. I suppose we could make EXPLAIN throw error
there, instead of printing a node type we'd have to document.

regards, tom lane

On Thu, Aug 20, 2009 at 12:40 PM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>
>
> Tom Lane wrote:
>>
>> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>>
>>>
>>> Updated version with more complete information (regression crash was due
>>> to my bad script).
>>>
>>
>> I took a look through the source code to match it against this.  I found
>> that you missed a couple of possibilities: we have <Notify /> and
>> <Utility-Statement /> as alternatives to <Query> just below <explain>.
>>
>
>
> What causes those to happen? Here's how I mangled the regression tests to
> produce the output that this analysis was taken from:
>
>   perl -spi.bak -e 's/^(insert|update|select|delete|declare|execute|create
> table .* as) /explain (analyse true, verbose true, format xml) $1 /i;' *.sql

CREATE RULE foo_notify AS ON UPDATE TO foo DO ALSO NOTIFY bob;

I am not sure that there's any way to get any other kind of utility
statement in there; I think that's just a safety valve in case someone
changes the rule mechanism and forgets to update EXPLAIN.

...Robert