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