Обсуждение: parse tree to XML format
Is there some way to export the postgresql query parse tree in XML format? I can not locate the API/Tool etc to do
that...
thanks
-Matt
On Monday 28 December 2009 22:30:44 matt wrote: > Is there some way to export the postgresql query parse tree in XML format? > I can not locate the API/Tool etc to do that... Thats more of a -general question. There is no such possibility in 8.4 - the not yet released 8.5 contains such a possibility. Andres
On Mon, Dec 28, 2009 at 7:32 PM, Andres Freund <andres@anarazel.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 format? >> I can not locate the API/Tool etc to do that... > 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. ...Robert
On Tuesday 29 December 2009 01:35:25 Robert Haas wrote: > On Mon, Dec 28, 2009 at 7:32 PM, Andres Freund <andres@anarazel.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 > >> format? I can not locate the API/Tool etc to do that... > > > > 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. You can play around with "debug_print_parse" but thats many things but definitely not xml. Matt, what are you trying to achieve? Andres
On Tuesday 29 December 2009 15:30:00 matt wrote: > --- On Mon, 12/28/09, Andres Freund <andres@anarazel.de> wrote: > > From: Andres Freund <andres@anarazel.de> > > Subject: Re: [HACKERS] parse tree to XML format > > To: pgsql-hackers@postgresql.org > > Cc: "Robert Haas" <robertmhaas@gmail.com>, "matt" <nuc233@yahoo.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@anarazel.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
--- On Mon, 12/28/09, Andres Freund <andres@anarazel.de> wrote:
> From: Andres Freund <andres@anarazel.de>
> Subject: Re: [HACKERS] parse tree to XML format
> To: pgsql-hackers@postgresql.org
> Cc: "Robert Haas" <robertmhaas@gmail.com>, "matt" <nuc233@yahoo.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@anarazel.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
> > >> format? I can not locate the API/Tool etc to
> do that...
> > >
> > > 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.
>
> You can play around with "debug_print_parse" but thats many
> things but
> definitely not xml.
>
> 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
easierto figure that on queries that are in some format else we have to effectively parse the queries ourself or hack
thepostgresql 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,
anypointers what i need to do to get the XML?
regards
Matt
>
> Andres
>
2009/12/29 matt <nuc233@yahoo.com>: > > > --- On Mon, 12/28/09, Andres Freund <andres@anarazel.de> wrote: > >> From: Andres Freund <andres@anarazel.de> >> Subject: Re: [HACKERS] parse tree to XML format >> To: pgsql-hackers@postgresql.org >> Cc: "Robert Haas" <robertmhaas@gmail.com>, "matt" <nuc233@yahoo.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@anarazel.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 >> > >> format? I can not locate the API/Tool etc to >> do that... >> > > >> > > 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. >> >> You can play around with "debug_print_parse" but thats many >> things but >> definitely not xml. >> >> 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 easierto figure that on queries that are in some format else we have to effectively parse the queries ourself or hack thepostgresql parser...which we dont want to do... It's wrong place - you have to parse execution plan. Parser knows nothing about index usage. maybe this link will be useful for you http://archives.postgresql.org/pgsql-patches/2007-05/msg00421.php Regards Pavel Stehule > > 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, anypointers what i need to do to get the XML? > > regards > Matt > > >> >> Andres >> > > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
matt wrote: > We are trying to gather statistics about our queries and get automatic suggestions for what indexes to utilize ...its easierto figure that on queries that are in some format else we have to effectively parse the queries ourself or hack thepostgresql parser...which we dont want to do... > You don't want the parse tree at all then; you want the query plans, as shown by EXPLAIN, which is a completely different thing. I'm a bit concerned you've got blinders on to what path you're going to take to work on this problem. Getting EXPLAIN plans out in machine readable format solves only a tiny fraction of the things you need to figure out in order to select better indexes. You'd be better off instrumenting your existing server with log analysis tools instead whether or not they include that specific format, rather than chasing after a feature only added in a version you can't put into production yet. There's a couple of ways to log information about the queries that are taking a long time to execute listed at http://wiki.postgresql.org/wiki/Logging_Difficult_Queries that you can use to help sort through finding the queries that are taking a lot of resources. Another helpful bit you should know about is that you can save log files in CSV format, which makes them easier to import for later analysis: http://www.postgresql.org/docs/current/static/runtime-config-logging.html > 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, anypointers what i need to do to get the XML? > http://developer.postgresql.org/pgdocs/postgres/sql-explain.html The "FORMAT XML" is what you want here. Not sure how easy it is to combine that with auto-explain. I just wrote something yesterday about a tool I created to make testing these pre-releases easier at http://notemagnet.blogspot.com/2009/12/testing-postgresql-85-alpha3-with-peg.html you might find helpful for your evaluation. Unless you have a good way to simulate your production app against a test server, I'm not sure what wandering down this path will accomplish for you though. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com