May 2018
Intermediate to advanced
576 pages
30h 25m
English
Another thing of interest that you may want to look for is long-running queries. To get a list of running queries ordered by how long they have been executing, use the following:
SELECT current_timestamp - query_start AS runtime, datname, usename, queryFROM pg_stat_activityWHERE state = 'active'ORDER BY 1 DESC;
This will return currently running queries, with the longest running queries in the front.
On busy systems, you may want to limit the set of queries returned to only the first few queries (add LIMIT 10 at the end) or only the queries that have been running over a certain period of time. For example, to get a list of queries that have been running for more than a minute, use this query:
SELECT current_timestamp ...