Thread: 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
Index: contrib/auto_explain/auto_explain.c
===================================================================
RCS file: /cvsroot/pgsql/contrib/auto_explain/auto_explain.c,v
retrieving revision 1.7
diff -c -r1.7 auto_explain.c
*** contrib/auto_explain/auto_explain.c 10 Aug 2009 05:46:49 -0000 1.7
--- contrib/auto_explain/auto_explain.c 17 Aug 2009 21:19:08 -0000
***************
*** 222,228 ****
--- 222,230 ----
es.verbose = auto_explain_log_verbose;
es.format = auto_explain_log_format;
+ ExplainBeginOutput(&es);
ExplainPrintPlan(&es, queryDesc);
+ ExplainEndOutput(&es);
/* Remove last line break */
if (es.str->len > 0 && es.str->data[es.str->len - 1] == '\n')
Index: src/backend/commands/explain.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/explain.c,v
retrieving revision 1.189
diff -c -r1.189 explain.c
*** src/backend/commands/explain.c 10 Aug 2009 05:46:50 -0000 1.189
--- src/backend/commands/explain.c 17 Aug 2009 21:19:09 -0000
***************
*** 91,98 ****
bool labeled, ExplainState *es);
static void ExplainDummyGroup(const char *objtype, const char *labelname,
ExplainState *es);
- static void ExplainBeginOutput(ExplainState *es);
- static void ExplainEndOutput(ExplainState *es);
static void ExplainXMLTag(const char *tagname, int flags, ExplainState *es);
static void ExplainJSONLineEnding(ExplainState *es);
static void escape_json(StringInfo buf, const char *str);
--- 91,96 ----
***************
*** 1712,1718 ****
* This is just enough different from processing a subgroup that we need
* a separate pair of subroutines.
*/
! static void
ExplainBeginOutput(ExplainState *es)
{
switch (es->format)
--- 1710,1716 ----
* This is just enough different from processing a subgroup that we need
* a separate pair of subroutines.
*/
! void
ExplainBeginOutput(ExplainState *es)
{
switch (es->format)
***************
*** 1739,1745 ****
/*
* Emit the end-of-output boilerplate.
*/
! static void
ExplainEndOutput(ExplainState *es)
{
switch (es->format)
--- 1737,1743 ----
/*
* Emit the end-of-output boilerplate.
*/
! void
ExplainEndOutput(ExplainState *es)
{
switch (es->format)
Index: src/include/commands/explain.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/explain.h,v
retrieving revision 1.41
diff -c -r1.41 explain.h
*** src/include/commands/explain.h 10 Aug 2009 05:46:50 -0000 1.41
--- src/include/commands/explain.h 17 Aug 2009 21:19:09 -0000
***************
*** 54,59 ****
--- 54,63 ----
extern void ExplainInitState(ExplainState *es);
+ extern void ExplainBeginOutput(ExplainState *es);
+
+ extern void ExplainEndOutput(ExplainState *es);
+
extern TupleDesc ExplainResultDesc(ExplainStmt *stmt);
extern void ExplainOneUtility(Node *utilityStmt, ExplainState *es,
Andrew Dunstan <andrew.dunstan@pgexperts.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.dunstan@pgexperts.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@dunslane.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@dunslane.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@dunslane.net> wrote: > > Tom Lane wrote: >> >> Andrew Dunstan <andrew.dunstan@pgexperts.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 HaganderMe: 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian <bruce@momjian.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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Andrew Dunstan <andrew@dunslane.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 } | elementFilter { 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+ } | elementSort-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 } | elementTotal-Cost { xsd:decimal })*, element Index-Cond { text }? } Item = element Item { text }
On Thu, Aug 20, 2009 at 9:30 AM, Andrew Dunstan<andrew@dunslane.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@dunslane.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 } | elementCTE-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 } | elementSort-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@dunslane.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@dunslane.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@dunslane.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 likeCREATE 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@dunslane.net> wrote: > > > Tom Lane wrote: >> >> Andrew Dunstan <andrew@dunslane.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