PostgreSQL 11 Administration Cookbook

Watching the longest queries

Another point 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 code:

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 that are 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 the following query: ...