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. Therefore, it is also possible to use the variable in a more fine-grained way:

test=# ALTER DATABASE test SET log_min_duration_statement...