launchpad-dev team mailing list archive

[Stuart Bishop <stuart.bishop@xxxxxxxxxxxxx>]

On Wed, Sep 1, 2010 at 8:22 AM, Martin Pool <mbp@xxxxxxxxxxxxx> wrote:
> On 1 September 2010 10:23, Edwin Grubbs <edwin.grubbs@xxxxxxxxxxxxx> wrote:
>> On Tue, Aug 10, 2010 at 9:54 PM, Robert Collins
>> <robert.collins@xxxxxxxxxxxxx> wrote:
>>> On Tue, Aug 10, 2010 at 11:40 PM, Tom Haddon <tom.haddon@xxxxxxxxxxxxx> 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 <stuart@xxxxxxxxxxxxxxxx>
http://www.stuartbishop.net/