Re: parse tree to XML format

On Tuesday 29 December 2009 15:30:00 matt wrote:
> --- On Mon, 12/28/09, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > From: Andres Freund <andres(at)anarazel(dot)de>
> > Subject: Re: [HACKERS] parse tree to XML format
> > To: pgsql-hackers(at)postgresql(dot)org
> > Cc: "Robert Haas" <robertmhaas(at)gmail(dot)com>, "matt" <nuc233(at)yahoo(dot)com>
> > Date: Monday, December 28, 2009, 7:39 PM
> > On Tuesday 29 December 2009 01:35:25
> >
> > Robert Haas wrote:
> > > On Mon, Dec 28, 2009 at 7:32 PM, Andres Freund <andres(at)anarazel(dot)de>
> >
> > wrote:
> > > > On Monday 28 December 2009 22:30:44 matt wrote:
> > > > > Is there some way to export the postgresql
> > > > > query parse tree in XML
> > > > Thats more of a -general question. There is no such possibility in 8.4
> > > > - the not yet released 8.5 contains such a possibility.
> > > Well, you can export the plan as XML using EXPLAIN (FORMAT XML), but
> > > that's not the same thing as the query parse-tree.
> > Uh. Err. Sorry.
> > Matt, what are you trying to achieve?
> We are trying to gather statistics about our queries and get automatic
> suggestions for what indexes to utilize ...its easier to figure that on
> queries that are in some format else we have to effectively parse the
> queries ourself or hack the postgresql parser...which we dont want to
> do...

> Did you mention that the 8.5 code has such a functionality? i would like to
> download the code and play with it a bit, any pointers what i need to do
> to get the XML?
Well, I didnt read carefully enough to recognize that you only want the parse
tree and not the post-optimization query plan. The latter one you can get by
using EXPLAIN (FORMAT XML) $query, but thats quite possibly not what you want
to get.

Other than setting debug_print_parse might be interesting - that does output a
pure text ormat though.

Another approach for such suggestions is to scan the statistic tables to i.e.
look for tables which are sequentially scanned or such.

Andres