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 this 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 logline for each query that exceeds this threshold. By default, the slow query log is off, as follows:

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 may, of course, vary.

In...