Checking for slow queries

After inspecting pg_stat_activity, it makes sense to take a look at slow time-consuming queries. Basically, there are two ways to approach the problem:

• Look for individual slow queries in the log
• Look for types of queries that take too much time

Finding single, slow queries is the classic approach to performance tuning. By setting the log_min_duration_statement variable to a desired threshold, PostgreSQL will start to write a log line for each query exceeding this threshold. By default, the slow-query log is off:

test=# SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
                         -1
(1 row)

However, setting this variable to a reasonably good value makes perfect sense. Depending on your workload, the desired time might of course vary.

In many cases, the desired value might differ from database to database...