pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPossible request for new feature
3+ messages / 2 participants
[nested] [flat]
* Possible request for new feature
@ 2014-11-05 15:25 Campbell, Lance <lance@illinois.edu>
2014-11-05 15:39 ` Re: Possible request for new feature Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Campbell, Lance @ 2014-11-05 15:25 UTC (permalink / raw)
To: 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<http://illinois.edu/person/lance;
Software Architect
Web Services at Public Affairs
217-333-0382
[University of Illinois at Urbana-Champaign logo]<http://illinois.edu/;
Attachments:
image001.png (image/png, 1.4K) download
view image
* Re: Possible request for new feature
2014-11-05 15:25 Possible request for new feature Campbell, Lance <lance@illinois.edu>
@ 2014-11-05 15:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-11-05 16:15 ` Re: Possible request for new feature Campbell, Lance <lance@illinois.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2014-11-05 15:39 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-admin
"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 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.
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Possible request for new feature
2014-11-05 15:25 Possible request for new feature Campbell, Lance <lance@illinois.edu>
2014-11-05 15:39 ` Re: Possible request for new feature Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-11-05 16:15 ` Campbell, Lance <lance@illinois.edu>
0 siblings, 0 replies; 3+ messages in thread
From: Campbell, Lance @ 2014-11-05 16:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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 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.
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-11-05 16:15 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-11-05 15:25 Possible request for new feature Campbell, Lance <lance@illinois.edu>
2014-11-05 15:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-11-05 16:15 ` Campbell, Lance <lance@illinois.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox