Thread: how to find the order of joins from Explain command XML plan output in PostgreSQL

<div dir="ltr">"EXPLAIN (format XML) " command in PostgreSQL9.3.4 gives the plan chosen by the optimizer in XML format.
Inmy program, I have to extract certain data about optimizer plan from this XML output. I am using
<strong>LibXML2</strong>library for parsing the XML. I had successfully extracted information about which relations are
involvedand what joins are used by parsing the XML. But I am <strong>unable to extract the</strong> <strong>order of
joiningthe relations from the XML output</strong>. I conceptually understood that the reverse level order traversal of
binarytree representation of the XML plan will give correct ordering of joins applied. But I could not figure out how
doI get that from the XML? Does libXML2 support anything of this sort? If not how should I proceed to tackle this?<br
/></div>

csrajmohan wrote
> "EXPLAIN (format XML) " command in PostgreSQL9.3.4 gives the plan chosen
> by
> the optimizer in XML format. In my program, I have to extract certain data
> about optimizer plan from this XML output. I am using *LibXML2* library
> for
> parsing the XML. I had successfully extracted information about which
> relations are involved and what joins are used by parsing the XML. But
> I am *unable
> to extract the* *order of joining the relations from the XML output*. I
> conceptually understood that the reverse level order traversal of binary
> tree representation of the XML plan will give correct ordering of joins
> applied. But I could not figure out how do I get that from the XML? Does
> libXML2 support anything of this sort? If not how should I proceed to
> tackle this?

So, since nothing better has been forthcoming in your other two posts on
this topic I'll just say that likely you will have much better luck using
SAX-based processing as opposed to DOM-based processing.  I seriously doubt
native/core PostgreSQL facilities will allow you to do what you desire.

As you said, hierarchy and physical output order determines the "order of
joining" within the planner so you have to capture and track such relational
information during your processing - which is made much easier if you simply
traverse the output node-by-node exactly as a SAX based parser does.

Though pgAdminIII has a visual query display that you might look at for
inspiration.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-find-the-order-of-joins-from-Explain-command-XML-plan-output-in-PostgreSQL-tp5811053p5811056.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

<p dir="ltr">Le 9 juil. 2014 20:36, "David G Johnston" <<a
href="mailto:david.g.johnston@gmail.com">david.g.johnston@gmail.com</a>>a écrit :<br /> ><br /> > csrajmohan
wrote<br/> > > "EXPLAIN (format XML) " command in PostgreSQL9.3.4 gives the plan chosen<br /> > > by<br />
>> the optimizer in XML format. In my program, I have to extract certain data<br /> > > about optimizer
planfrom this XML output. I am using *LibXML2* library<br /> > > for<br /> > > parsing the XML. I had
successfullyextracted information about which<br /> > > relations are involved and what joins are used by parsing
theXML. But<br /> > > I am *unable<br /> > > to extract the* *order of joining the relations from the XML
output*.I<br /> > > conceptually understood that the reverse level order traversal of binary<br /> > > tree
representationof the XML plan will give correct ordering of joins<br /> > > applied. But I could not figure out
howdo I get that from the XML? Does<br /> > > libXML2 support anything of this sort? If not how should I proceed
to<br/> > > tackle this?<br /> ><br /> > So, since nothing better has been forthcoming in your other two
postson<br /> > this topic I'll just say that likely you will have much better luck using<br /> > SAX-based
processingas opposed to DOM-based processing.  I seriously doubt<br /> > native/core PostgreSQL facilities will
allowyou to do what you desire.<br /> ><br /> > As you said, hierarchy and physical output order determines the
"orderof<br /> > joining" within the planner so you have to capture and track such relational<br /> > information
duringyour processing - which is made much easier if you simply<br /> > traverse the output node-by-node exactly as
aSAX based parser does.<br /> ><br /> > Though pgAdminIII has a visual query display that you might look at
for<br/> > inspiration.<br /> ><p dir="ltr">FWIW, pgadmin's visual explain doesn't (yet?) use XML or json or yaml
output.