up vote 4 down vote favorite
4
share [g+] share [fb]

I'm in the process of reviewing every SQL statement that an application makes against the database, for performance reasons. Is there an easy way to log all statements that are executed by the PostgreSQL database server? Thanks.

link|improve this question
feedback

2 Answers

up vote 8 down vote accepted

The config option you're looking for is log_statement = "all" (if you just want the statements), or log_min_statement_duration = <some number> if you're just after "slow" queries (for some value of "slow"). See http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html for more details on logging configuration.

link|improve this answer
Logging all statements is a performance killer (as stated in the official docs). However, 8.4 has a nice feature of getting the explain analyze of a slow query at the tie it was executed, you might want to start testing with this as 8.4 isn't yet released but it's a nice option to know that happend at the time of execution, if the analyze explain output is OK you probably are running into issues with I/O or CPU bounds, but at least you'll know it's not the query itself – Server Horror Jun 14 '09 at 14:05
1  
I really like the log_statement = 'mod' option. It only shows creates, updates, and deletes, and skips all the select statements. Great if you're trying to figure out which code is tweaking some field. – Don Kirkby Aug 6 '09 at 21:48
feedback
up vote 3 down vote

Of course, you can detect slowest queries by yourself, but I advise you to use pgFouine — a PostgreSQL log analyzer. It`s easy to install and really useful.

Sample reports: here and here.

link|improve this answer
I used pgFouine on a project and identified several places where an index would help things a lot. – Paul Tomblin Jun 4 '09 at 21:54
feedback

Your Answer

 
or
required, but never shown

Not the answer you're looking for? Browse other questions tagged or ask your own question.