Explain XML patch v2
This is an update to my EXPLAIN XML patch submitted a few days ago.
I've added a documentation patch and modified some of the code per
comments by Gregory Stark.
Because the main consumer of output generated by this patch will
presumably be a machine, I didn't clutter up the documentation with
matching XML output for every standard example query. But, I added
enough to hopefully give the user an idea of what to expect.
Regards,
Tom Raney
Attachments:
exml_patch_v2text/plain; charset=UTF-8; name=exml_patch_v2Download
*** doc/src/sgml/perform.sgml.orig 2008-07-01 20:27:19.000000000 -0700
--- doc/src/sgml/perform.sgml 2008-07-01 20:34:44.000000000 -0700
***************
*** 47,59 ****
operations on the raw rows, then there will be additional nodes
<quote>atop</> the scan nodes to perform these operations. Again,
there is usually more than one possible way to do these operations,
! so different node types can appear here too. The output
of <command>EXPLAIN</command> has one line for each node in the plan
tree, showing the basic node type plus the cost estimates that the planner
made for the execution of that plan node. The first line (topmost node)
has the estimated total execution cost for the plan; it is this number
that the planner seeks to minimize.
</para>
<para>
Here is a trivial example, just to show what the output looks like.
--- 47,62 ----
operations on the raw rows, then there will be additional nodes
<quote>atop</> the scan nodes to perform these operations. Again,
there is usually more than one possible way to do these operations,
! so different node types can appear here too. The standard output
of <command>EXPLAIN</command> has one line for each node in the plan
tree, showing the basic node type plus the cost estimates that the planner
made for the execution of that plan node. The first line (topmost node)
has the estimated total execution cost for the plan; it is this number
that the planner seeks to minimize.
</para>
+ <para>
+ For examples of XML output, see the bottom of this page.
+ </para>
<para>
Here is a trivial example, just to show what the output looks like.
***************
*** 448,453 ****
--- 451,513 ----
process the table in any case, so there's no value in expending additional
page reads to look at an index.
</para>
+
+ <para>
+ Examples of XML output:
+ <programlisting>
+ EXPLAIN XML SELECT * FROM tenk1;
+
+ QUERY PLAN
+ -------------------------------------------------------------
+ <![CDATA[ <?xml version="1.0"?>
+
+ <explain version="x.x">
+ <plan name="Seq Scan" indent="0">
+ <table name="tenk1"/>
+ <cost startup="0.00" total="458.00" rows="10000" width="244" />
+ </plan>
+ </explain>
+ (8 rows)]]>
+ </programlisting>
+ </para>
+ <para>
+ <programlisting>
+ EXPLAIN ANALYZE XML SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
+
+ QUERY PLAN
+ -------------------------------------------------------------
+ <![CDATA[ <?xml version="1.0"?>
+
+ <explain version="x.x">
+ <plan name="Nested Loop" indent="0">
+ <cost startup="5.03" total="693.17" rows="100" width="488" />
+ <analyze time_start="0.981" time_end="6.118" rows="100" loops="1" />
+ </plan>
+ <plan name="Bitmap Heap Scan" indent="3">
+ <table name="tenk1" alias="t1"/>
+ <cost startup="5.03" total="221.07" rows="100" width="244" />
+ <analyze time_start="0.826" time_end="2.226" rows="100" loops="1" />
+ <qualifier type="Recheck Cond" value="(unique1 < 100)" />
+ </plan>
+ <plan name="Bitmap Index Scan" indent="6">
+ <index name="tenk1_unique1" />
+ <cost startup="0.00" total="5.00" rows="100" width="0" />
+ <analyze time_start="0.663" time_end="0.663" rows="100" loops="1" />
+ <qualifier type="Index Cond" value="(unique1 < 100)" />
+ </plan>
+ <plan name="Index Scan" indent="3">
+ <index name="tenk2_unique2" />
+ <table name="tenk2" alias="t2"/>
+ <cost startup="0.00" total="4.71" rows="1" width="244" />
+ <analyze time_start="0.020" time_end="0.022" rows="1" loops="100" />
+ <qualifier type="Index Cond" value="(t2.unique2 = t1.unique2)" />
+ </plan>
+ <runtime ms="7.204" />
+ </explain>
+ (28 rows)]]>
+ </programlisting>
+ </para>
+
</sect1>
<sect1 id="planner-stats">
*** doc/src/sgml/ref/explain.sgml.orig 2008-07-01 20:29:14.000000000 -0700
--- doc/src/sgml/ref/explain.sgml 2008-06-29 20:05:37.000000000 -0700
***************
*** 30,36 ****
<refsynopsisdiv>
<synopsis>
! EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="parameter">statement</replaceable>
</synopsis>
</refsynopsisdiv>
--- 30,36 ----
<refsynopsisdiv>
<synopsis>
! EXPLAIN [ ANALYZE ] [ VERBOSE ] [ XML [ DTD ] ] <replaceable class="parameter">statement</replaceable>
</synopsis>
</refsynopsisdiv>
***************
*** 112,117 ****
--- 112,135 ----
</varlistentry>
<varlistentry>
+ <term><literal>XML</literal></term>
+ <listitem>
+ <para>
+ Emit XML output instead of the standard output.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DTD</literal></term>
+ <listitem>
+ <para>
+ Emit the optional DTD (Document Type Definition) for the XML output.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">statement</replaceable></term>
<listitem>
<para>
***************
*** 183,188 ****
--- 201,225 ----
</para>
<para>
+ The same query with XML output:
+ <programlisting>
+ EXPLAIN XML SELECT * FROM foo;
+
+ QUERY PLAN
+ ---------------------------------------------------------
+ <![CDATA[<?xml version="1.0"?>
+
+ <explain version="x.x">
+ <plan name="Seq Scan" indent="0">
+ <table name="foo"/>
+ <cost startup="0.00" total="155.00" rows="10000" width="4" />
+ </plan>
+ </explain>
+ (8 rows)]]>
+ </programlisting>
+ </para>
+
+ <para>
If there is an index and we use a query with an indexable
<literal>WHERE</literal> condition, <command>EXPLAIN</command>
might show a different plan:
*** src/backend/nodes/copyfuncs.c.orig 2008-06-26 18:18:19.000000000 -0700
--- src/backend/nodes/copyfuncs.c 2008-06-26 07:26:46.000000000 -0700
***************
*** 2568,2573 ****
--- 2568,2575 ----
COPY_NODE_FIELD(query);
COPY_SCALAR_FIELD(verbose);
COPY_SCALAR_FIELD(analyze);
+ COPY_SCALAR_FIELD(xml);
+ COPY_SCALAR_FIELD(dtd);
return newnode;
}
*** src/backend/nodes/equalfuncs.c.orig 2008-06-26 18:18:39.000000000 -0700
--- src/backend/nodes/equalfuncs.c 2008-06-26 07:25:33.000000000 -0700
***************
*** 1358,1363 ****
--- 1358,1365 ----
COMPARE_NODE_FIELD(query);
COMPARE_SCALAR_FIELD(verbose);
COMPARE_SCALAR_FIELD(analyze);
+ COMPARE_SCALAR_FIELD(xml);
+ COMPARE_SCALAR_FIELD(dtd);
return true;
}
*** src/backend/commands/explain.c.orig 2008-06-10 09:59:12.000000000 -0700
--- src/backend/commands/explain.c 2008-06-26 15:39:38.000000000 -0700
***************
*** 45,50 ****
--- 45,51 ----
/* options */
bool printTList; /* print plan targetlists */
bool printAnalyze; /* print actual times */
+ bool printXML; /* print output as XML */
/* other states */
PlannedStmt *pstmt; /* top of plan */
List *rtable; /* range table */
***************
*** 54,60 ****
const char *queryString,
ParamListInfo params, TupOutputState *tstate);
static void report_triggers(ResultRelInfo *rInfo, bool show_relname,
! StringInfo buf);
static double elapsed_time(instr_time *starttime);
static void explain_outNode(StringInfo str,
Plan *plan, PlanState *planstate,
--- 55,61 ----
const char *queryString,
ParamListInfo params, TupOutputState *tstate);
static void report_triggers(ResultRelInfo *rInfo, bool show_relname,
! StringInfo buf, bool show_xml);
static double elapsed_time(instr_time *starttime);
static void explain_outNode(StringInfo str,
Plan *plan, PlanState *planstate,
***************
*** 67,78 ****
StringInfo str, int indent, ExplainState *es);
static void show_upper_qual(List *qual, const char *qlabel, Plan *plan,
StringInfo str, int indent, ExplainState *es);
! static void show_sort_keys(Plan *sortplan, int nkeys, AttrNumber *keycols,
const char *qlabel,
StringInfo str, int indent, ExplainState *es);
- static void show_sort_info(SortState *sortstate,
- StringInfo str, int indent, ExplainState *es);
static const char *explain_get_index_name(Oid indexId);
/*
--- 68,79 ----
StringInfo str, int indent, ExplainState *es);
static void show_upper_qual(List *qual, const char *qlabel, Plan *plan,
StringInfo str, int indent, ExplainState *es);
! static void show_sort_keys(SortState *sortstate, Plan *sortplan, int nkeys, AttrNumber *keycols,
const char *qlabel,
StringInfo str, int indent, ExplainState *es);
static const char *explain_get_index_name(Oid indexId);
+ static void show_dtd(StringInfo str);
+
/*
***************
*** 269,280 ****
es->printTList = stmt->verbose;
es->printAnalyze = stmt->analyze;
es->pstmt = queryDesc->plannedstmt;
es->rtable = queryDesc->plannedstmt->rtable;
initStringInfo(&buf);
! explain_outNode(&buf,
! queryDesc->plannedstmt->planTree, queryDesc->planstate,
NULL, 0, es);
/*
--- 270,293 ----
es->printTList = stmt->verbose;
es->printAnalyze = stmt->analyze;
+ es->printXML = stmt->xml;
es->pstmt = queryDesc->plannedstmt;
es->rtable = queryDesc->plannedstmt->rtable;
initStringInfo(&buf);
!
! if (stmt->xml) {
! appendStringInfo (&buf, "<?xml version=\"1.0\"?>\n\n");
!
! /* Only include the DTD if the user *really* wants it */
! if (stmt->dtd)
! show_dtd(&buf);
!
! appendStringInfo (&buf, "<explain version=\"%s\">\n", PG_VERSION);
! }
!
!
! explain_outNode(&buf, queryDesc->plannedstmt->planTree, queryDesc->planstate,
NULL, 0, es);
/*
***************
*** 302,313 ****
show_relname = (numrels > 1 || targrels != NIL);
rInfo = queryDesc->estate->es_result_relations;
for (nr = 0; nr < numrels; rInfo++, nr++)
! report_triggers(rInfo, show_relname, &buf);
foreach(l, targrels)
{
rInfo = (ResultRelInfo *) lfirst(l);
! report_triggers(rInfo, show_relname, &buf);
}
}
--- 315,326 ----
show_relname = (numrels > 1 || targrels != NIL);
rInfo = queryDesc->estate->es_result_relations;
for (nr = 0; nr < numrels; rInfo++, nr++)
! report_triggers(rInfo, show_relname, &buf, stmt->xml);
foreach(l, targrels)
{
rInfo = (ResultRelInfo *) lfirst(l);
! report_triggers(rInfo, show_relname, &buf, stmt->xml);
}
}
***************
*** 330,337 ****
totaltime += elapsed_time(&starttime);
if (stmt->analyze)
! appendStringInfo(&buf, "Total runtime: %.3f ms\n",
1000.0 * totaltime);
do_text_output_multiline(tstate, buf.data);
pfree(buf.data);
--- 343,359 ----
totaltime += elapsed_time(&starttime);
if (stmt->analyze)
! {
! if (stmt->xml)
! appendStringInfo(&buf, "<runtime ms=\"%.3f\" />\n",
! 1000.0 * totaltime);
! else
! appendStringInfo(&buf, "Total runtime: %.3f ms\n",
1000.0 * totaltime);
+ }
+ if (stmt->xml)
+ appendStringInfo(&buf, "</explain>\n");
+
do_text_output_multiline(tstate, buf.data);
pfree(buf.data);
***************
*** 343,349 ****
* report execution stats for a single relation's triggers
*/
static void
! report_triggers(ResultRelInfo *rInfo, bool show_relname, StringInfo buf)
{
int nt;
--- 365,371 ----
* report execution stats for a single relation's triggers
*/
static void
! report_triggers(ResultRelInfo *rInfo, bool show_relname, StringInfo buf, bool show_xml)
{
int nt;
***************
*** 354,359 ****
--- 376,383 ----
Trigger *trig = rInfo->ri_TrigDesc->triggers + nt;
Instrumentation *instr = rInfo->ri_TrigInstrument + nt;
char *conname;
+ StringInfo triggerStr;
+ triggerStr = makeStringInfo();
/* Must clean up instrumentation state */
InstrEndLoop(instr);
***************
*** 368,385 ****
if (OidIsValid(trig->tgconstraint) &&
(conname = get_constraint_name(trig->tgconstraint)) != NULL)
{
! appendStringInfo(buf, "Trigger for constraint %s", conname);
pfree(conname);
}
! else
! appendStringInfo(buf, "Trigger %s", trig->tgname);
!
! if (show_relname)
! appendStringInfo(buf, " on %s",
RelationGetRelationName(rInfo->ri_RelationDesc));
! appendStringInfo(buf, ": time=%.3f calls=%.0f\n",
1000.0 * instr->total, instr->ntuples);
}
}
--- 392,433 ----
if (OidIsValid(trig->tgconstraint) &&
(conname = get_constraint_name(trig->tgconstraint)) != NULL)
{
! if (!show_xml)
! appendStringInfo(buf, "Trigger for constraint %s", conname);
! else
! appendStringInfo(triggerStr, "constraint=\"%s\"", conname);
pfree(conname);
}
! else {
! if (!show_xml)
! appendStringInfo(buf, "Trigger %s", trig->tgname);
! else
! appendStringInfo(triggerStr, "name=\"%s\"", trig->tgname);
! }
! if (show_relname)
! {
! if (!show_xml)
! appendStringInfo(buf, " on %s",
! RelationGetRelationName(rInfo->ri_RelationDesc));
! else
! appendStringInfo(triggerStr, " on=\"%s\"",
RelationGetRelationName(rInfo->ri_RelationDesc));
! }
!
! if (show_xml)
! appendStringInfo(buf, " <trigger %s "
! "time=%.3f calls=%.0f />\n",
! triggerStr->data,
! 1000.0 * instr->total,
! instr->ntuples);
! else
! appendStringInfo(buf, ": time=%.3f calls=%.0f\n",
1000.0 * instr->total, instr->ntuples);
+
+
+ pfree(triggerStr->data);
+ pfree(triggerStr);
}
}
***************
*** 417,423 ****
if (plan == NULL)
{
! appendStringInfoChar(str, '\n');
return;
}
--- 465,475 ----
if (plan == NULL)
{
! if (es->printXML)
! appendStringInfo(str, "<plan />\n");
! else
! appendStringInfoChar(str, '\n');
!
return;
}
***************
*** 588,601 ****
break;
}
! appendStringInfoString(str, pname);
switch (nodeTag(plan))
{
case T_IndexScan:
if (ScanDirectionIsBackward(((IndexScan *) plan)->indexorderdir))
! appendStringInfoString(str, " Backward");
! appendStringInfo(str, " using %s",
! explain_get_index_name(((IndexScan *) plan)->indexid));
/* FALL THRU */
case T_SeqScan:
case T_BitmapHeapScan:
--- 640,677 ----
break;
}
! if (es->printXML)
! appendStringInfo(str, "<plan name=\"%s\" indent=\"%d\">\n", pname, indent);
! else
! appendStringInfoString(str, pname);
!
switch (nodeTag(plan))
{
case T_IndexScan:
+ {
+ StringInfo index;
+ index = makeStringInfo();
+ appendStringInfo(index, "name=\"%s\"", explain_get_index_name(((IndexScan *) plan)->indexid));
+
if (ScanDirectionIsBackward(((IndexScan *) plan)->indexorderdir))
! {
! if (es->printXML)
! appendStringInfoString(index, " backward");
! else
! appendStringInfoString(str, " Backward");
!
! }
!
! if (es->printXML)
! appendStringInfo(str, " <index %s />\n",
! index->data);
! else
! appendStringInfo(str, " using %s",
! explain_get_index_name(((IndexScan *) plan)->indexid));
!
! pfree(index->data);
! pfree(index);
! }
/* FALL THRU */
case T_SeqScan:
case T_BitmapHeapScan:
***************
*** 605,610 ****
--- 681,689 ----
RangeTblEntry *rte = rt_fetch(((Scan *) plan)->scanrelid,
es->rtable);
char *relname;
+ StringInfo resname;
+
+ resname = makeStringInfo();
/* Assume it's on a real relation */
Assert(rte->rtekind == RTE_RELATION);
***************
*** 612,627 ****
/* We only show the rel name, not schema name */
relname = get_rel_name(rte->relid);
! appendStringInfo(str, " on %s",
quote_identifier(relname));
if (strcmp(rte->eref->aliasname, relname) != 0)
! appendStringInfo(str, " %s",
! quote_identifier(rte->eref->aliasname));
}
break;
case T_BitmapIndexScan:
! appendStringInfo(str, " on %s",
! explain_get_index_name(((BitmapIndexScan *) plan)->indexid));
break;
case T_SubqueryScan:
if (((Scan *) plan)->scanrelid > 0)
--- 691,733 ----
/* We only show the rel name, not schema name */
relname = get_rel_name(rte->relid);
! if (es->printXML)
! {
! appendStringInfo(resname, "name=\"%s\"",
! quote_identifier(relname));
! } else
! {
! appendStringInfo(str, " on %s",
quote_identifier(relname));
+ }
+
+
if (strcmp(rte->eref->aliasname, relname) != 0)
! {
! if (es->printXML)
! appendStringInfo(resname, " alias=\"%s\"",
! quote_identifier(rte->eref->aliasname));
! else
! appendStringInfo(str, " %s",
! quote_identifier(rte->eref->aliasname));
! }
!
! if (es->printXML)
! appendStringInfo(str, " <table %s/>\n",
! resname->data);
!
! pfree(resname->data);
! pfree(resname);
}
break;
case T_BitmapIndexScan:
! if (es->printXML)
! appendStringInfo(str, " <index name=\"%s\" />\n",
! explain_get_index_name(((BitmapIndexScan *) plan)->indexid));
! else
! appendStringInfo(str, " on %s",
! explain_get_index_name(((BitmapIndexScan *) plan)->indexid));
!
break;
case T_SubqueryScan:
if (((Scan *) plan)->scanrelid > 0)
***************
*** 629,636 ****
RangeTblEntry *rte = rt_fetch(((Scan *) plan)->scanrelid,
es->rtable);
! appendStringInfo(str, " %s",
quote_identifier(rte->eref->aliasname));
}
break;
case T_FunctionScan:
--- 735,747 ----
RangeTblEntry *rte = rt_fetch(((Scan *) plan)->scanrelid,
es->rtable);
! if (es->printXML)
! appendStringInfo(str, " <table alias=\"%s\" />\n",
quote_identifier(rte->eref->aliasname));
+ else
+ appendStringInfo(str, " %s",
+ quote_identifier(rte->eref->aliasname));
+
}
break;
case T_FunctionScan:
***************
*** 641,646 ****
--- 752,761 ----
Node *funcexpr;
char *proname;
+ StringInfo resname;
+
+ resname = makeStringInfo();
+
/* Assert it's on a RangeFunction */
Assert(rte->rtekind == RTE_FUNCTION);
***************
*** 661,671 ****
else
proname = rte->eref->aliasname;
! appendStringInfo(str, " on %s",
quote_identifier(proname));
if (strcmp(rte->eref->aliasname, proname) != 0)
! appendStringInfo(str, " %s",
quote_identifier(rte->eref->aliasname));
}
break;
case T_ValuesScan:
--- 776,805 ----
else
proname = rte->eref->aliasname;
! if (es->printXML)
! appendStringInfo(resname, "name=\"%s\"",
! quote_identifier(proname));
! else
! appendStringInfo(str, " on %s",
quote_identifier(proname));
+
if (strcmp(rte->eref->aliasname, proname) != 0)
! {
! if (es->printXML)
! appendStringInfo(resname, " alias=\"%s\"",
! quote_identifier(rte->eref->aliasname));
! else
! appendStringInfo(str, " %s",
quote_identifier(rte->eref->aliasname));
+
+ }
+
+ if (es->printXML)
+ appendStringInfo(str, " <function %s />\n",
+ resname->data);
+ pfree(resname->data);
+ pfree(resname);
+
}
break;
case T_ValuesScan:
***************
*** 680,686 ****
valsname = rte->eref->aliasname;
! appendStringInfo(str, " on %s",
quote_identifier(valsname));
}
break;
--- 814,824 ----
valsname = rte->eref->aliasname;
! if (es->printXML)
! appendStringInfo(str, "name=\"%s\"",
! quote_identifier(valsname));
! else
! appendStringInfo(str, " on %s",
quote_identifier(valsname));
}
break;
***************
*** 688,694 ****
break;
}
! appendStringInfo(str, " (cost=%.2f..%.2f rows=%.0f width=%d)",
plan->startup_cost, plan->total_cost,
plan->plan_rows, plan->plan_width);
--- 826,838 ----
break;
}
! if (es->printXML)
! appendStringInfo(str, " <cost startup=\"%.2f\" total=\"%.2f\" "
! "rows=\"%.0f\" width=\"%d\" />\n",
! plan->startup_cost, plan->total_cost,
! plan->plan_rows, plan->plan_width);
! else
! appendStringInfo(str, " (cost=%.2f..%.2f rows=%.0f width=%d)",
plan->startup_cost, plan->total_cost,
plan->plan_rows, plan->plan_width);
***************
*** 703,717 ****
{
double nloops = planstate->instrument->nloops;
! appendStringInfo(str, " (actual time=%.3f..%.3f rows=%.0f loops=%.0f)",
! 1000.0 * planstate->instrument->startup / nloops,
! 1000.0 * planstate->instrument->total / nloops,
! planstate->instrument->ntuples / nloops,
! planstate->instrument->nloops);
}
else if (es->printAnalyze)
! appendStringInfo(str, " (never executed)");
! appendStringInfoChar(str, '\n');
/* target list */
if (es->printTList)
--- 847,878 ----
{
double nloops = planstate->instrument->nloops;
! if (es->printXML)
! appendStringInfo(str,
! " <analyze time_start=\"%.3f\" time_end=\"%.3f\" "
! "rows=\"%.0f\" loops=\"%.0f\" />\n",
! 1000.0 * planstate->instrument->startup / nloops,
! 1000.0 * planstate->instrument->total / nloops,
! planstate->instrument->ntuples / nloops,
! planstate->instrument->nloops);
! else
! appendStringInfo(str, " (actual time=%.3f..%.3f rows=%.0f loops=%.0f)",
! 1000.0 * planstate->instrument->startup / nloops,
! 1000.0 * planstate->instrument->total / nloops,
! planstate->instrument->ntuples / nloops,
! planstate->instrument->nloops);
}
else if (es->printAnalyze)
! {
! if (es->printXML)
! appendStringInfo(str, " <analyze never />");
! else
! appendStringInfo(str, " (never executed)");
!
! }
!
! if (!es->printXML)
! appendStringInfoChar(str, '\n');
/* target list */
if (es->printTList)
***************
*** 823,835 ****
str, indent, es);
break;
case T_Sort:
! show_sort_keys(plan,
((Sort *) plan)->numCols,
((Sort *) plan)->sortColIdx,
"Sort Key",
str, indent, es);
- show_sort_info((SortState *) planstate,
- str, indent, es);
break;
case T_Result:
show_upper_qual((List *) ((Result *) plan)->resconstantqual,
--- 984,994 ----
str, indent, es);
break;
case T_Sort:
! show_sort_keys((SortState *) planstate, plan,
((Sort *) plan)->numCols,
((Sort *) plan)->sortColIdx,
"Sort Key",
str, indent, es);
break;
case T_Result:
show_upper_qual((List *) ((Result *) plan)->resconstantqual,
***************
*** 843,856 ****
break;
}
/* initPlan-s */
if (plan->initPlan)
{
ListCell *lst;
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " InitPlan\n");
foreach(lst, planstate->initPlan)
{
SubPlanState *sps = (SubPlanState *) lfirst(lst);
--- 1002,1023 ----
break;
}
+ if (es->printXML)
+ appendStringInfo(str, "</plan>\n");
+
/* initPlan-s */
if (plan->initPlan)
{
ListCell *lst;
! if (!es->printXML)
! {
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
!
! appendStringInfo(str, " InitPlan\n");
! }
!
foreach(lst, planstate->initPlan)
{
SubPlanState *sps = (SubPlanState *) lfirst(lst);
***************
*** 858,864 ****
for (i = 0; i < indent; i++)
appendStringInfo(str, " ");
! appendStringInfo(str, " -> ");
explain_outNode(str,
exec_subplan_get_plan(es->pstmt, sp),
sps->planstate,
--- 1025,1034 ----
for (i = 0; i < indent; i++)
appendStringInfo(str, " ");
!
! if (!es->printXML)
! appendStringInfo(str, " -> ");
!
explain_outNode(str,
exec_subplan_get_plan(es->pstmt, sp),
sps->planstate,
***************
*** 870,878 ****
/* lefttree */
if (outerPlan(plan))
{
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " -> ");
/*
* Ordinarily we don't pass down our own outer_plan value to our child
--- 1040,1052 ----
/* lefttree */
if (outerPlan(plan))
{
!
! if (!es->printXML)
! {
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " -> ");
! }
/*
* Ordinarily we don't pass down our own outer_plan value to our child
***************
*** 888,896 ****
/* righttree */
if (innerPlan(plan))
{
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " -> ");
explain_outNode(str, innerPlan(plan),
innerPlanState(planstate),
outerPlan(plan),
--- 1062,1073 ----
/* righttree */
if (innerPlan(plan))
{
! if (!es->printXML)
! {
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " -> ");
! }
explain_outNode(str, innerPlan(plan),
innerPlanState(planstate),
outerPlan(plan),
***************
*** 909,917 ****
{
Plan *subnode = (Plan *) lfirst(lst);
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " -> ");
/*
* Ordinarily we don't pass down our own outer_plan value to our
--- 1086,1097 ----
{
Plan *subnode = (Plan *) lfirst(lst);
! if (!es->printXML)
! {
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " -> ");
! }
/*
* Ordinarily we don't pass down our own outer_plan value to our
***************
*** 939,947 ****
{
Plan *subnode = (Plan *) lfirst(lst);
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " -> ");
explain_outNode(str, subnode,
bitmapandstate->bitmapplans[j],
--- 1119,1130 ----
{
Plan *subnode = (Plan *) lfirst(lst);
! if (!es->printXML)
! {
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " -> ");
! }
explain_outNode(str, subnode,
bitmapandstate->bitmapplans[j],
***************
*** 963,971 ****
{
Plan *subnode = (Plan *) lfirst(lst);
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " -> ");
explain_outNode(str, subnode,
bitmaporstate->bitmapplans[j],
--- 1146,1157 ----
{
Plan *subnode = (Plan *) lfirst(lst);
! if (!es->printXML)
! {
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " -> ");
! }
explain_outNode(str, subnode,
bitmaporstate->bitmapplans[j],
***************
*** 981,989 ****
SubqueryScanState *subquerystate = (SubqueryScanState *) planstate;
Plan *subnode = subqueryscan->subplan;
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " -> ");
explain_outNode(str, subnode,
subquerystate->subplan,
--- 1167,1178 ----
SubqueryScanState *subquerystate = (SubqueryScanState *) planstate;
Plan *subnode = subqueryscan->subplan;
! if (!es->printXML)
! {
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " -> ");
! }
explain_outNode(str, subnode,
subquerystate->subplan,
***************
*** 996,1004 ****
{
ListCell *lst;
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " SubPlan\n");
foreach(lst, planstate->subPlan)
{
SubPlanState *sps = (SubPlanState *) lfirst(lst);
--- 1185,1196 ----
{
ListCell *lst;
! if (!es->printXML)
! {
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " SubPlan\n");
! }
foreach(lst, planstate->subPlan)
{
SubPlanState *sps = (SubPlanState *) lfirst(lst);
***************
*** 1006,1012 ****
for (i = 0; i < indent; i++)
appendStringInfo(str, " ");
! appendStringInfo(str, " -> ");
explain_outNode(str,
exec_subplan_get_plan(es->pstmt, sp),
sps->planstate,
--- 1198,1206 ----
for (i = 0; i < indent; i++)
appendStringInfo(str, " ");
!
! if (!es->printXML)
! appendStringInfo(str, " -> ");
explain_outNode(str,
exec_subplan_get_plan(es->pstmt, sp),
sps->planstate,
***************
*** 1042,1050 ****
useprefix = list_length(es->rtable) > 1;
/* Emit line prefix */
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " Output: ");
/* Deparse each non-junk result column */
i = 0;
--- 1236,1252 ----
useprefix = list_length(es->rtable) > 1;
/* Emit line prefix */
!
! if (es->printXML)
! {
! appendStringInfo(str, " <output>\n");
! }
! else
! {
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " Output: ");
! }
/* Deparse each non-junk result column */
i = 0;
***************
*** 1054,1067 ****
if (tle->resjunk)
continue;
! if (i++ > 0)
! appendStringInfo(str, ", ");
! appendStringInfoString(str,
! deparse_expression((Node *) tle->expr, context,
! useprefix, false));
}
! appendStringInfoChar(str, '\n');
}
/*
--- 1256,1282 ----
if (tle->resjunk)
continue;
!
! if (es->printXML)
! {
! appendStringInfo(str, " <col name=\"%s\" />\n",
! deparse_expression((Node *) tle->expr,
! context, useprefix, false));
! }
! else
! {
! if (i++ > 0)
! appendStringInfo(str, ", ");
! appendStringInfoString(str,
! deparse_expression((Node *) tle->expr,
! context, useprefix, false));
! }
}
! if (es->printXML)
! appendStringInfo(str, " </output>\n");
! else
! appendStringInfoChar(str, '\n');
}
/*
***************
*** 1099,1107 ****
exprstr = deparse_expression(node, context, useprefix, false);
/* And add to str */
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " %s: %s\n", qlabel, exprstr);
}
/*
--- 1314,1329 ----
exprstr = deparse_expression(node, context, useprefix, false);
/* And add to str */
!
! if (es->printXML)
! appendStringInfo(str," <qualifier type=\"%s\" value=\"%s\" />\n",
! qlabel, exprstr);
! else
! {
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " %s: %s\n", qlabel, exprstr);
! }
}
/*
***************
*** 1132,1147 ****
exprstr = deparse_expression(node, context, useprefix, false);
/* And add to str */
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " %s: %s\n", qlabel, exprstr);
}
/*
* Show the sort keys for a Sort node.
*/
static void
! show_sort_keys(Plan *sortplan, int nkeys, AttrNumber *keycols,
const char *qlabel,
StringInfo str, int indent, ExplainState *es)
{
--- 1354,1377 ----
exprstr = deparse_expression(node, context, useprefix, false);
/* And add to str */
!
! if (es->printXML)
! appendStringInfo(str," <qualifier type=\"%s\" value=\"%s\" />\n",
! qlabel, exprstr);
!
! else
! {
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " %s: %s\n", qlabel, exprstr);
! }
}
/*
* Show the sort keys for a Sort node.
*/
static void
! show_sort_keys(SortState *sortstate, Plan *sortplan, int nkeys, AttrNumber *keycols,
const char *qlabel,
StringInfo str, int indent, ExplainState *es)
{
***************
*** 1150,1162 ****
int keyno;
char *exprstr;
int i;
if (nkeys <= 0)
return;
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " %s: ", qlabel);
/* Set up deparsing context */
context = deparse_context_for_plan((Node *) outerPlan(sortplan),
--- 1380,1430 ----
int keyno;
char *exprstr;
int i;
+ StringInfo condition;
if (nkeys <= 0)
return;
! if (es->printXML)
! appendStringInfo(str," <sort type=\"%s\"",
! qlabel);
!
! /*
! * If it's EXPLAIN ANALYZE, show tuplesort explain info for a sort node
! */
! Assert(IsA(sortstate, SortState));
! if (es->printAnalyze && sortstate->sort_Done &&
! sortstate->tuplesortstate != NULL)
! {
! char *sortinfo;
! int i;
!
! sortinfo = tuplesort_explain((Tuplesortstate *) sortstate->tuplesortstate);
!
! if (es->printXML)
! {
! appendStringInfo(str, " desc=\"%s\" ", sortinfo);
! }
! else
! {
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, "%s\n", sortinfo);
!
! }
! pfree(sortinfo);
! }
!
! if (es->printXML)
! appendStringInfo(str," />\n");
! else
! {
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " %s: ", qlabel);
! }
!
!
/* Set up deparsing context */
context = deparse_context_for_plan((Node *) outerPlan(sortplan),
***************
*** 1164,1169 ****
--- 1432,1439 ----
es->rtable);
useprefix = list_length(es->rtable) > 1;
+ condition = makeStringInfo();
+
for (keyno = 0; keyno < nkeys; keyno++)
{
/* find key expression in tlist */
***************
*** 1175,1209 ****
/* Deparse the expression, showing any top-level cast */
exprstr = deparse_expression((Node *) target->expr, context,
useprefix, true);
! /* And add to str */
! if (keyno > 0)
! appendStringInfo(str, ", ");
! appendStringInfoString(str, exprstr);
}
! appendStringInfo(str, "\n");
! }
!
! /*
! * If it's EXPLAIN ANALYZE, show tuplesort explain info for a sort node
! */
! static void
! show_sort_info(SortState *sortstate,
! StringInfo str, int indent, ExplainState *es)
! {
! Assert(IsA(sortstate, SortState));
! if (es->printAnalyze && sortstate->sort_Done &&
! sortstate->tuplesortstate != NULL)
! {
! char *sortinfo;
! int i;
! sortinfo = tuplesort_explain((Tuplesortstate *) sortstate->tuplesortstate);
! for (i = 0; i < indent; i++)
! appendStringInfo(str, " ");
! appendStringInfo(str, " %s\n", sortinfo);
! pfree(sortinfo);
! }
}
/*
--- 1445,1469 ----
/* Deparse the expression, showing any top-level cast */
exprstr = deparse_expression((Node *) target->expr, context,
useprefix, true);
!
! if (es->printXML)
! appendStringInfo(condition, " <key number=\"%d\">%s</key>\n", keyno, exprstr);
! else
! {
! /* And add to str */
! if (keyno > 0)
! appendStringInfo(str, ", ");
! appendStringInfoString(str, exprstr);
! }
}
! if (es->printXML)
! appendStringInfo(str,"%s </sort>\n", condition->data);
! else
! appendStringInfo(str, "\n");
! pfree(condition->data);
! pfree(condition);
}
/*
***************
*** 1231,1233 ****
--- 1491,1552 ----
}
return result;
}
+
+ /*
+ * Outputs the DTD for the EXPLAIN XML output
+ *
+ */
+
+ static void
+ show_dtd(StringInfo str)
+ {
+
+ appendStringInfo(str, "<!DOCTYPE explain\n"
+ "[\n"
+ "<!ELEMENT explain (plan+, runtime?) >\n"
+ "<!ELEMENT plan (table?, index?, cost, output?, sort?, analyze?, qualifier?) >\n"
+ "<!ELEMENT table EMPTY >\n"
+ "<!ELEMENT cost EMPTY >\n"
+ "<!ELEMENT qualifier EMPTY >\n"
+ "<!ELEMENT output (col+) >\n"
+ "<!ELEMENT col EMPTY >\n"
+ "<!ELEMENT analyze EMPTY >\n"
+ "<!ELEMENT runtime EMPTY >\n"
+ "<!ELEMENT index EMPTY >\n"
+ "<!ELEMENT sort (key+) >\n"
+ "<!ELEMENT key (#PCDATA) >\n"
+ "<!ATTLIST explain\n"
+ " version CDATA #REQUIRED >\n"
+ "<!ATTLIST plan\n"
+ " name CDATA #REQUIRED\n"
+ " indent CDATA #REQUIRED >\n"
+ "<!ATTLIST cost\n"
+ " startup CDATA #REQUIRED\n"
+ " total CDATA #REQUIRED\n"
+ " rows CDATA #REQUIRED\n"
+ " width CDATA #REQUIRED >\n"
+ "<!ATTLIST table\n"
+ " name CDATA #REQUIRED\n"
+ " alias CDATA #IMPLIED>\n"
+ "<!ATTLIST qualifier\n"
+ " type CDATA #REQUIRED\n"
+ " value CDATA #REQUIRED >\n"
+ "<!ATTLIST col\n"
+ " name CDATA #REQUIRED >\n"
+ "<!ATTLIST analyze\n"
+ " time_start CDATA #REQUIRED\n"
+ " time_end CDATA #REQUIRED\n"
+ " rows CDATA #REQUIRED\n"
+ " loops CDATA #REQUIRED >\n"
+ "<!ATTLIST runtime\n"
+ " ms CDATA #REQUIRED >\n"
+ "<!ATTLIST index\n"
+ " name CDATA #REQUIRED >\n"
+ "<!ATTLIST sort\n"
+ " type CDATA #REQUIRED >\n"
+ "<!ATTLIST key\n"
+ " number CDATA #REQUIRED >\n"
+ "]>\n\n");
+
+
+ }
*** src/bin/psql/tab-complete.c.orig 2008-06-10 09:59:14.000000000 -0700
--- src/bin/psql/tab-complete.c 2008-06-26 08:01:25.000000000 -0700
***************
*** 1541,1552 ****
/* EXPLAIN */
/*
! * Complete EXPLAIN [ANALYZE] [VERBOSE] with list of EXPLAIN-able commands
*/
else if (pg_strcasecmp(prev_wd, "EXPLAIN") == 0)
{
static const char *const list_EXPLAIN[] =
! {"SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE", "ANALYZE", "VERBOSE", NULL};
COMPLETE_WITH_LIST(list_EXPLAIN);
}
--- 1541,1552 ----
/* EXPLAIN */
/*
! * Complete EXPLAIN [ANALYZE] [VERBOSE] [XML [DTD]] with list of EXPLAIN-able commands
*/
else if (pg_strcasecmp(prev_wd, "EXPLAIN") == 0)
{
static const char *const list_EXPLAIN[] =
! {"SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE", "ANALYZE", "VERBOSE", "XML", "DTD", NULL};
COMPLETE_WITH_LIST(list_EXPLAIN);
}
***************
*** 1554,1560 ****
pg_strcasecmp(prev_wd, "ANALYZE") == 0)
{
static const char *const list_EXPLAIN[] =
! {"SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE", "VERBOSE", NULL};
COMPLETE_WITH_LIST(list_EXPLAIN);
}
--- 1554,1560 ----
pg_strcasecmp(prev_wd, "ANALYZE") == 0)
{
static const char *const list_EXPLAIN[] =
! {"SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE", "VERBOSE", "XML", "DTD", NULL};
COMPLETE_WITH_LIST(list_EXPLAIN);
}
*** src/include/nodes/parsenodes.h.orig 2008-06-10 09:59:07.000000000 -0700
--- src/include/nodes/parsenodes.h 2008-06-26 07:28:42.000000000 -0700
***************
*** 1871,1876 ****
--- 1871,1878 ----
Node *query; /* the query (as a raw parse tree) */
bool verbose; /* print plan info */
bool analyze; /* get statistics by executing plan */
+ bool xml; /* get the output as XML instead of plain text */
+ bool dtd; /* include the DTD for the XML output */
} ExplainStmt;
/* ----------------------
*** src/backend/parser/gram.y.orig 2008-06-26 18:59:41.000000000 -0700
--- src/backend/parser/gram.y 2008-07-01 19:50:01.000000000 -0700
***************
*** 282,287 ****
--- 282,288 ----
%type <boolean> opt_instead opt_analyze
%type <boolean> index_opt_unique opt_verbose opt_full
%type <boolean> opt_freeze opt_default opt_recheck
+ %type <boolean> opt_xml opt_dtd
%type <defelt> opt_binary opt_oids copy_delimiter
%type <boolean> copy_from
***************
*** 388,393 ****
--- 389,395 ----
DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC
DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP
+ DTD
EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EXCEPT EXCLUDING
EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
***************
*** 5787,5802 ****
/*****************************************************************************
*
* QUERY:
! * EXPLAIN [ANALYZE] [VERBOSE] query
*
*****************************************************************************/
! ExplainStmt: EXPLAIN opt_analyze opt_verbose ExplainableStmt
{
ExplainStmt *n = makeNode(ExplainStmt);
n->analyze = $2;
n->verbose = $3;
! n->query = $4;
$$ = (Node *)n;
}
;
--- 5789,5806 ----
/*****************************************************************************
*
* QUERY:
! * EXPLAIN [ANALYZE] [VERBOSE] [XML [DTD]] query
*
*****************************************************************************/
! ExplainStmt: EXPLAIN opt_analyze opt_verbose opt_xml opt_dtd ExplainableStmt
{
ExplainStmt *n = makeNode(ExplainStmt);
n->analyze = $2;
n->verbose = $3;
! n->xml = $4;
! n->dtd = $5;
! n->query = $6;
$$ = (Node *)n;
}
;
***************
*** 5815,5820 ****
--- 5819,5834 ----
| /* EMPTY */ { $$ = FALSE; }
;
+ opt_xml:
+ XML_P { $$ = TRUE; }
+ | /*EMPTY*/ { $$ = FALSE; }
+ ;
+
+ opt_dtd:
+ DTD { $$ = TRUE; }
+ | /*EMPTY*/ { $$ = FALSE; }
+ ;
+
/*****************************************************************************
*
* QUERY:
***************
*** 9019,9024 ****
--- 9033,9039 ----
| DOMAIN_P
| DOUBLE_P
| DROP
+ | DTD
| EACH
| ENABLE_P
| ENCODING
*** src/backend/parser/keywords.c.orig 2008-06-26 19:00:01.000000000 -0700
--- src/backend/parser/keywords.c 2008-07-01 19:47:46.000000000 -0700
***************
*** 146,151 ****
--- 146,152 ----
{"domain", DOMAIN_P, UNRESERVED_KEYWORD},
{"double", DOUBLE_P, UNRESERVED_KEYWORD},
{"drop", DROP, UNRESERVED_KEYWORD},
+ {"dtd", DTD, UNRESERVED_KEYWORD},
{"each", EACH, UNRESERVED_KEYWORD},
{"else", ELSE, RESERVED_KEYWORD},
{"enable", ENABLE_P, UNRESERVED_KEYWORD},
*** src/interfaces/ecpg/preproc/preproc.y.orig 2008-06-26 20:20:49.000000000 -0700
--- src/interfaces/ecpg/preproc/preproc.y 2008-07-01 19:47:02.000000000 -0700
***************
*** 434,439 ****
--- 434,440 ----
DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC
DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP
+ DTD
EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EXCEPT EXCLUSIVE EXCLUDING
EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
"Tom Raney" <raneyt@cecs.pdx.edu> writes:
This is an update to my EXPLAIN XML patch submitted a few days ago.
I've added a documentation patch and modified some of the code per comments by
Gregory Stark.
You should update the wiki at
http://wiki.postgresql.org/wiki/CommitFest:2008-07
so any reviewers look at the updated patch.
(I certainly don't see any reason to wait two months for the next commit fest)
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!
Am Mittwoch, 2. Juli 2008 schrieb Tom Raney:
This is an update to my EXPLAIN XML patch submitted a few days ago.
Could you explain how you came up with the XML schema design? I suppose you
just made something up that went along with the existing XML output.
I would like to see more integration with the spirit of the existing XML
functionality. For example, instead of things like
"<runtime ms=\"%.3f\" />\n"
we ought to be using XML Schema data types for time intervals and so on.
We might also want to use an XML namespace.
Table and index names should be escaped using the existing escape mechanism
for identifiers. There might also be encoding issues.
It would also be interesting if EXPLAIN could optionally be a function that
returns a datum of type XML, to allow further processing.
Any thoughts on these issues?
On Wed, Jul 02, 2008 at 05:57:29PM +0200, Peter Eisentraut wrote:
It would also be interesting if EXPLAIN could optionally be a
function that returns a datum of type XML, to allow further
processing.
It would be better to have a function which allows people to plug in
their own serialization. A JSON or YAML one, for example, would be
much lighter weight on both ends.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Wed, Jul 2, 2008 at 4:57 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
Am Mittwoch, 2. Juli 2008 schrieb Tom Raney:
This is an update to my EXPLAIN XML patch submitted a few days ago.
Could you explain how you came up with the XML schema design? I suppose you
just made something up that went along with the existing XML output.
Speaking of schema - I haven't had time to review the patch myself
yet, but does it include schema names for all relations? The current
text output does not (and adding it has been rejected due to
verbosity), but that makes any kind of query tuning or information
gathering tool more or less impossible to write.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
On Wed, Jul 02, 2008 at 09:01:18AM -0700, David Fetter wrote:
On Wed, Jul 02, 2008 at 05:57:29PM +0200, Peter Eisentraut wrote:
It would also be interesting if EXPLAIN could optionally be a
function that returns a datum of type XML, to allow further
processing.It would be better to have a function which allows people to plug in
their own serialization. A JSON or YAML one, for example, would be
much lighter weight on both ends.
+1 for either of these.
-dg
--
David Gould daveg@sonic.net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.
Peter Eisentraut wrote:
Am Mittwoch, 2. Juli 2008 schrieb Tom Raney:
This is an update to my EXPLAIN XML patch submitted a few days ago.
Could you explain how you came up with the XML schema design? I suppose you
just made something up that went along with the existing XML output.
Yes, it is based on the existing output.
I would like to see more integration with the spirit of the existing XML
functionality. For example, instead of things like"<runtime ms=\"%.3f\" />\n"
we ought to be using XML Schema data types for time intervals and so on.
The DTD provides only rudimentary document validation but it has no
support for type checking. So, it may make sense to move to the more
rigorous XML Schema. There is a 'duration' data type that could be used
for the instance listed above. Or, we could define our own.
We might also want to use an XML namespace.
Taking the 'ms' field listed above:
xmlns="http://www.postgresql.org/v8.4/ms" or something like this?
Table and index names should be escaped using the existing escape mechanism
for identifiers. There might also be encoding issues.
That's a good point. Or, wrap them with CDATA.
It would also be interesting if EXPLAIN could optionally be a function that
returns a datum of type XML, to allow further processing.Any thoughts on these issues?
I am in the process of writing a parser of this XML output for the Red
Hat Visual Explain tool. I want to see what surprises come up during
implementation.
Quoting daveg <daveg@sonic.net>:
On Wed, Jul 02, 2008 at 09:01:18AM -0700, David Fetter wrote:
On Wed, Jul 02, 2008 at 05:57:29PM +0200, Peter Eisentraut wrote:
It would also be interesting if EXPLAIN could optionally be a
function that returns a datum of type XML, to allow further
processing.It would be better to have a function which allows people to plug in
their own serialization. A JSON or YAML one, for example, would be
much lighter weight on both ends.+1 for either of these.
-dg
So, this leads me to the idea of assembling the EXPLAIN data
internally in an output-neutral data structure. At the very end of
processing, one decision statement would decide which output plugin to
use for output. Sprinkling XML print statements throughout the code
(as currently done in the patch) while functional, is not ideal. And,
the escaping of XML content should ideally be done in the serializer
anyway.
Of course, this realization didn't occur to me until *after* I had
spent a bit of time coding up the patch in its current form. Oh well.
Thoughts?
Regarding the XML datum, in order to support that, will all users need
to compile with libxml? Are there any lighter weight solutions to
serialize other than libxml?
-Tom Raney
Am Freitag, 4. Juli 2008 schrieb Tom Raney:
Regarding the XML datum, in order to support that, will all users need �
to compile with libxml? �Are there any lighter weight solutions to �
serialize other than libxml?
You can create XML without libxml.
Peter Eisentraut <peter_e@gmx.net> writes:
Am Freitag, 4. Juli 2008 schrieb Tom Raney:
Regarding the XML datum, in order to support that, will all users need �
to compile with libxml? �Are there any lighter weight solutions to �
serialize other than libxml?
You can create XML without libxml.
Seems to me that anyone who wants this feature will probably also want
the existing libxml-based features, so they'll be building with libxml
anyway. So I'd not be in favor of expending any extra code on a
roll-your-own solution.
regards, tom lane
On Tue, 2008-07-01 at 21:48 -0700, Tom Raney wrote:
This is an update to my EXPLAIN XML patch submitted a few days ago.
This is an important patch and you've done well to get it this far.
I have much detailed feedback, but these are just emergent requests, now
I can see and understand what you've done.
* If the patch was implemented as an ExplainOneQuery_hook we would be
able to use this with 8.3 also, which might be interesting. So there's
no real need for this to be a patch on core.
* If I understand, the DTD option inlines the DTD into the resulting XML
document, rather than adding a schema definition?
* The DTD should be in a separate file also, so it can be referred to.
We should give that DTD a permanent URL so we can identify it across the
internet. This is the first time the project has published an XML
DTD/Schema, so we need to think through the right way to publish it. The
DTD should have a version number in it, so when this gets updated in the
future we can validate against the correct one.
* The DTD is regrettably a long, long way from where I need it to be.
The PLAN elements are all unrelated to one another, apart from their
sequence within the XML document and their "indent". That definition can
lead to XML documents that match the DTD yet would never be valid plans,
amongst other problems. For sensible analysis of the SQL we need the DTD
to match the actual structure of nodes in the executor. This requires
major DTD changes, regrettably. The "indent" comes from the nesting of
the nodes, and is not merely a visual feature of the complete plan. IMHO
it is critically important that the XML correctly conveys the structure
of the plan and not just the formatting of the current text output.
Otherwise many doors will be closed to us and this whole project wasted.
I want to be able to write xml queries to retrieve plans that contain a
merge join where both the inner and outer are merge joins, or to easily
compare the structure of two complex queries looking for differences.
* The meaning of the two time attributes is somewhat confused. It is
startuptime and totaltime, not time_start and time_end.
* It looks to me like QUALIFIER alone is not enough, since in some cases
nodes have both an index condition and a filter, for example.
* I've made a number of suggested changes below, but the DTD really
needs to follow the structures in src/include/nodes/plannodes.h
In particular you'll see the recursive structure introduced by the DTD
changes below
* EXPLAIN has been renamed PLAN
* PLAN has been renamed NODE
* COST has been renamed to ESTIMATE
* ANALYZE has been renamed to ACTUAL
* OUTPUT, SORT and QUALIFIER have been removed for clarity only
<!ELEMENT plan (estimate, runtime?)>
<!ELEMENT node (table?, index?, estimate, actual?, outerpath?, innerpath?, initpath)>
<!ATTLIST plan\n"
nodetype CDATA #REQUIRED>
<!ELEMENT outerpath (node)>
<!ELEMENT innerpath (node)>
<!ELEMENT initpath (node)>
<!ELEMENT estimate EMPTY >
<!ATTLIST estimate
startupcost CDATA #REQUIRED
totalcost CDATA #REQUIRED
rows CDATA #REQUIRED
width CDATA #REQUIRED>
<!ELEMENT actual EMPTY >
<!ATTLIST actual\n"
startuptime CDATA #REQUIRED
totaltime CDATA #REQUIRED
rows CDATA #REQUIRED
loops CDATA #REQUIRED>
* I'd much prefer to define this as a Schema, since that's a bit more
flexible in what we can do, plus we can specify datatypes.
* Based upon some of those issues, I'd suggest that further work on the
DTD/Schema should only happen when a reasonable range of plans have been
accumulated to allow full understanding of the possibilities
I'm sorry I've found so much to say about this, but don't be dissuaded.
The basic structure of the patch is there, we just need to get the
details right also, so we can take full.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
On Fri, Jul 4, 2008 at 5:20 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
* If the patch was implemented as an ExplainOneQuery_hook we would be
able to use this with 8.3 also, which might be interesting. So there's
no real need for this to be a patch on core.
Would that mean that instead of being an optional format that pgAdmin
could request with appropriate grammar, it would be something that was
only available if the plugin was loaded, and would replace regular
EXPLAIN output? If so, that would be extremely inconvenient, and next
to useless for general purpose utilities.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
On Fri, 2008-07-04 at 19:38 +0100, Dave Page wrote:
On Fri, Jul 4, 2008 at 5:20 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
* If the patch was implemented as an ExplainOneQuery_hook we would be
able to use this with 8.3 also, which might be interesting. So there's
no real need for this to be a patch on core.Would that mean that instead of being an optional format that pgAdmin
could request with appropriate grammar, it would be something that was
only available if the plugin was loaded, and would replace regular
EXPLAIN output? If so, that would be extremely inconvenient, and next
to useless for general purpose utilities.
It can be optional since plugins can add parameters also.
It wouldn't take long to make up a plugin for 8.3 once this patch has
been committed to core for 8.4, so if you're saying you'd definitely
like it in core then I'm OK with that.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
On Fri, 2008-07-04 at 17:20 +0100, Simon Riggs wrote:
<!ELEMENT plan (estimate, runtime?)>
Sorry, I noticed a few typos in my sample DTD.
<!ELEMENT plan (node+)> with some additional elements for stats
and the attlist below was for the <node> not <plan> element
<!ATTLIST node
nodetype CDATA #REQUIRED>
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
On Sat, Jul 5, 2008 at 10:41 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
It can be optional since plugins can add parameters also.
GUCs I assume you mean, not grammar. Unless I'm misreading the code
though, if the plugin is there it will always run instead of the
regular explain code, so presumabiy that's optional as in XML or
nothing, not XML or standard output.
It wouldn't take long to make up a plugin for 8.3 once this patch has
been committed to core for 8.4, so if you're saying you'd definitely
like it in core then I'm OK with that.
If i's always there it's definitely more useful to pgAdmin, and
doesn't require that we instruct users to install more server side
plugin code to use the features they want.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
On Sat, 2008-07-05 at 16:00 +0100, Dave Page wrote:
On Sat, Jul 5, 2008 at 10:41 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
It can be optional since plugins can add parameters also.
GUCs I assume you mean, not grammar. Unless I'm misreading the code
though, if the plugin is there it will always run instead of the
regular explain code, so presumabiy that's optional as in XML or
nothing, not XML or standard output.
You can easily make it switchable between XML and normal.
It wouldn't take long to make up a plugin for 8.3 once this patch has
been committed to core for 8.4, so if you're saying you'd definitely
like it in core then I'm OK with that.If i's always there it's definitely more useful to pgAdmin, and
doesn't require that we instruct users to install more server side
plugin code to use the features they want.
As I said, I'm OK with that.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support