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