parse tree to XML format

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

regards
Matt

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

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 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...

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

matt wrote:

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...

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, any pointers 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