On Wed, Sep 1, 2010 at 8:22 AM, Martin Pool <[email protected]> wrote: > On 1 September 2010 10:23, Edwin Grubbs <[email protected]> wrote: >> On Tue, Aug 10, 2010 at 9:54 PM, Robert Collins >> <[email protected]> wrote: >>> On Tue, Aug 10, 2010 at 11:40 PM, Tom Haddon <[email protected]> >>> wrote: >>>> On Tue, 2010-08-10 at 21:04 +1200, Robert Collins wrote: >>>> [...] >>>>> I helped wgrant out with some explain analyzes - it seems to me it >>>>> might be nice - handwaving - to permit explain-analyze calls by any >>>>> developer, but perhaps thats just insanity at this hour of the night >>>>> kicking in:) >>>> >>>> We'd really like to push fairly hard against this one. EXPLAIN ANALYZE >>>> actually runs the query against the DB, so it has the potential to cause >>>> performance problems if it's a badly constructed query, apart from >>>> anything else. >>> >>> Hell yeah. I was thinking *staging* only. Yes there can be performance >>> issues, but thats rather the point :) >>> >>> -Rob >> >> Please, please please do something like this. I waste so much time >> trying to optimize queries. A lot of the time is spent just trying to >> think of every possible piece of information I need up front, so that >> I don't have to harass the losas over and over again. We've talked >> about providing a test database before but I don't know what happened >> to that. One caveat of just allowing explain analyze queries is that >> you can't test whether adding an index will change the complex query >> plan. > > Is there any way to get postgres to record the query plan and/or cost > at the same time as executing the statement, rather than making you do > a separate expensive query just to analyze it? I see there are > options to log the query plan > <http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html>, > and they could possibly be turned on just for a single request (eg by > a url decorator) but I don't know if they actually give you enough > data to be useful.
We could turn on the logging for a single request. The problem is that the output goes to the PostgreSQL logs, not somewhere we can easily extract them. - Hook in the publisher to turn on plan logging at the start of selected requests, and turn off at the end. - Ensure PostgreSQL log rotation will happen frequently enough, size bound rather than time bound. - Script to extract query plans from the logs. Possibly deinterleave. A little tricky as PG logs are not particularly parseable. Maybe non-Debian default logging options could be useful, such as syslog. - Sync job to mirror the extracted query plan log to somewhere devs can see. -- Stuart Bishop <[email protected]> http://www.stuartbishop.net/ _______________________________________________ Mailing list: https://launchpad.net/~launchpad-dev Post to : [email protected] Unsubscribe : https://launchpad.net/~launchpad-dev More help : https://help.launchpad.net/ListHelp
