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