Long running Queries

Long running queries in PostgreSQL:-

1. Following queries will return currently running top 10 queries and longest running queries in the front.

select current_timestamp-query_start as runtime,

datname,usename, query FROM pg_stat_activity

where state='active'

order by 1 desc

limit 10;

2. To get list of queries that have been running more than 1 minute in PostgreSQL..

select current_timestamp-query_start as runtime,

datname,usename, query FROM pg_stat_activity

where state='active' and current_timestamp-query_start> '1 min'

order by 1 desc

limit 10;

3. To get list of queries that have been running more than 5 minutes in PostgreSQL.

select current_timestamp-query_start as runtime,

datname,usename, query FROM pg_stat_activity

where state='active' and current_timestamp-query_start> '5 min'

order by 1 desc

limit 10;

Pre 9.5 verions:

SELECT

  pid,

  now() - pg_stat_activity.query_start AS duration,

  query,

  state

FROM pg_stat_activity

WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';