Thread: Possible request for new feature
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,
Software Architect
Web Services at Public Affairs
217-333-0382
Attachment
"Campbell, Lance" <lance@illinois.edu> writes: > I develop web application for the University of Illinois. We use PostgreSQL on the backend to provide our web applicationwith data for around 3 million pages of dynamic web content per month. A nice feature would be the ability todo something just like EXPLAIN ANALYZE on queries with the slight difference of actually returning the results from thequery and appending the QUERY PLAN to the end of the query. Such a behavior would break just about every client application. Have you looked into using the auto_explain module, which instead logs query plans to the postmaster log? That's a far less invasive way of collecting plans for queries of live applications. pg_stat_statements would be worth a look too. regards, tom lane
Tom, Thanks so much. That was very useful. It is more useful than my suggested enhancement. Thanks, Lance Campbell Software Architect Web Services at Public Affairs 217-333-0382 -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Wednesday, November 05, 2014 9:40 AM To: Campbell, Lance Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Possible request for new feature "Campbell, Lance" <lance@illinois.edu> writes: > I develop web application for the University of Illinois. We use PostgreSQL on the backend to provide our web applicationwith data for around 3 million pages of dynamic web content per month. A nice feature would be the ability todo something just like EXPLAIN ANALYZE on queries with the slight difference of actually returning the results from thequery and appending the QUERY PLAN to the end of the query. Such a behavior would break just about every client application. Have you looked into using the auto_explain module, whichinstead logs query plans to the postmaster log? That's a far less invasive way of collecting plans for queries of liveapplications. pg_stat_statements would be worth a look too. regards, tom lane