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 may, 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...