Possible request for new feature - Mailing list pgsql-admin

PostgreSQL 9.3

The below request for enhancement to PostgreSQL my already be available in a different way.  If so please let me know so that I might take advantage of this.

I develop web application for the University of Illinois.  We use PostgreSQL on the backend to provide our web application with data for around 3 million pages of dynamic web content per month.  A nice feature would be the ability to do something just like EXPLAIN ANALYZE on queries with the slight difference of actually returning the results from the query and appending the QUERY PLAN to the end of the query.

Use Case:

We are updating a cloud based blogging tool.  Because of the extensive changes we will need to analyze particular dynamic queries once the tool is released.  I would like to add a startup parameter to my blogging tool that would append to the front of particular queries a directive like “EXPLAIN ANALYZE RESULTS”.  When these queries are executed the JDBC PostgreSQL driver would process the results like normal except that the QUERY PLAN report would be captured by the “statement” object.  The Java code could then access the query plan via a method like statement.getQueryPlan().  That way I could send to a log file the exact SQL statement that was executed as well as the report of the query plan associated with it.  This way I can now analyze real world queries and their resulted reports to identify if I need to make changes.  Once I feel the queries are running properly I can then remove my application startup parameter so that “EXPLAIN ANALYZE RESULTS” is no longer appended to the front of the particular queries.  This way I no longer log the query plan for those queries.

This would allow for great on the fly analysis of how particular queries are being executed.  There may already be a way to do what I am wanting.  If so please let me know.

My thought is I could modify my web applications so that if it sees a particular parameter I could then actually perform all particular queries twice.  I would first do a “EXPLAIN ANALYZE” on the dynamic query and log the report.  Then I could perform the query a second time without the “EXPLAIN ANALYZE”.  This would hurt performance but would give me real world data for those queries.  I could then turn the feature off when I feel I have the information I need.  But it would be better to do the above Use Case.

Thanks,

Lance Campbell

Software Architect

Web Services at Public Affairs

217-333-0382