How to get long running queries in PostgreSQL

To get a list of running queries in PostgreSQL which are ordered by how long they have been executing. We can get long running queries by calculating time difference between query star time and current time stamp.

select current_timestamp – query_start as runtime, datname,pid, usename, query from pg_stat_activity where query <> ‘idle’
order by 1 desc;

For old versions(pre 9.2)

select current_timestamp - query_start as runtime, datname,pid, usename, current_query from pg_stat_activity where current_query<>'idle' order by 1 desc;

To get the queries running greater than 10 minutes in PostgreSQL:

SELECT now() - query_start as "runtime", usename, datname, waiting, state, query FROM pg_stat_activity WHERE now() - query_start > '10 minutes'::interval ORDER BY runtime DESC;

To get top 10 long running queries:

select current_timestamp - query_start as runtime, datname,pid, usename, current_query from pg_stat_activity where current_query<>'idle' order by 1 desc limit 10;

Related Posts

CheapSexCams
Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: