1、查询当前所有正在执行的SQL运行时间、事务运行时间。
select datname,usename,client_addr,pid,query_start::text,extract(epoch from (now() - query_start)) as query_runtime,
xact_start::text,extract(epoch from(now() - xact_start)) as xact_runtime,state,query
from pg_stat_activity
where state not in('idle') and query_start is not null ;
2、查询当前所有执行超过2秒的SQL语句情况。
select datname,usename,client_addr,pid,query_start::text,extract(epoch from (now() - query_start)) as query_runtime,
xact_start::text,extract(epoch from(now() - xact_start)) as xact_runtime,state,query
from pg_stat_activity
where state not in('idle') and query_start is not null and query_runtime>2;