How to find (log) slow queries in PostgreSQL 8.x, 9.x

One of the most important factors when troubleshooting the performance of an application or website, is the performance of the database. Improper indexing or inefficient SQL queries can kill the performance of your application rendering it useless. It is amazing what a simple tweak or change to a SQL statement can do. I have seen more than 100x performance improvements just from a one word change in a query. So how you can identify which queries are running slowly in your system? That is easy, just turn on the logging in your PostgreSQL database. This method has been tested in versions 8.3 and 8.4, but should apply to even 9.x!

Enable slow query logging in PostgreSQL

• Open the file postgresql.conf file in your favorite text editor.
  Note: If you are having trouble finding the file, run the command: find / -name postgresql.conf
• Look for the line:
  #log_min_duration_statement = -1and replace it with:
  log_min_duration_statement = 100
• Save the file and reload the PostgreSQL configuration (Don’t worry, it won’t interrupt any active connections)
  service postgresql reload
• Go to the pg_log folder and open the latest log file. (i.e. /var/lib/pgsql/data/pg_log/)

Setting the value to 100 will log every query that takes 100ms or longer to execute. Depending on the number of slow queries you have, this may be overwhelming, so consider increasing this value.

If you need some assistance in optimizing your slow queries, feel free to post them in the comments section below!