Re: parse tree to XML format

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(at)2ndQuadrant(dot)com www.2ndQuadrant.com