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