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

"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?

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.

Le 9 juil. 2014 20:36, "David G Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> a
écrit :
>
> 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.
>

FWIW, pgadmin's visual explain doesn't (yet?) use XML or json or yaml
output.