From: Jessica Richard <rjessil@yahoo.com>
To: pgsql-admin@postgresql.org
Subject: how to figure out how long a query takes in the pg log file...
Date: Tue, 10 Mar 2009 16:24:57 -0700 (PDT)
Message-ID: <798506.30088.qm@web111308.mail.gq1.yahoo.com> (raw)
I need to identify the slowest queries by analyzing the postgre log file.
By the following log_line_prefix set up, I can see all the query contents and I can see the duration times:
log_line_prefix = '%u %d %h %m %c %x '
..
..
postgres testdb [local] 2009-03-10 14:36:35.110 EDT 49b6b31a.8ad 30898958 DETAIL: ! system usage stats:
postgres testdb [local] 2009-03-10 14:36:35.110 EDT 49b6b31a.8ad 30898958 LOG: statement: select count(*) from testtable;
postgres testdb [local] 2009-03-10 14:36:35.111 EDT 49b6b31a.8ad 30898958 LOG: PARSE ANALYSIS STATISTICS
postgres testdb [local] 2009-03-10 14:36:35.111 EDT 49b6b31a.8ad 30898958 DETAIL: ! system usage stats:
..
postgres testdb [local] 2009-03-10 14:36:47.935 EDT 49b6b31a.8ad 0 LOG: duration: 12825.670 ms
But I am unable to link he duration time taken by the original query. Even though I used the session id %c and the transaction id %x, when it comes to the duration line, the transaction id is always "0" -- I can't match it to the query line that has a non-zero transaction id.
I ideally I expect to see something in common between the duration line and the query line so that I can group them together --- then I will be able to see the slowest queries and look for the tuning options from there. Since all the queries take different amounts of time to finish, the duration time showed up few lines after the query line does not mean it was the time that query took. The same session id can have many different transaction ids.
Could some one out there help me on this?
Thanks a lot,
Jessica
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rjessil@yahoo.com
Subject: Re: how to figure out how long a query takes in the pg log file...
In-Reply-To: <798506.30088.qm@web111308.mail.gq1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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