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 log line 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 might, of course, vary.
In many cases, the desired value might differ...