pg慢查询

postgres 下面查询

按平均时间排序

select userid::regrole,dbid,total_time,calls,total_time/calls as avg_time,query from pg_stat_statements order by avg_time desc limit 20;

按全部时间排序

select userid::regrole,dbid,total_time,calls,total_time/calls as avg_time,query from pg_stat_statements order by total_time desc limit 20;

清空慢查询重新记录

select pg_stat_statements_reset()

如果临时需要，也可以 在对应数据库执行如下sql语句即可：

1，单条记录查询：根据queryid 查询 query  ：

            select query  from pg_stat_statements  where queryid = '3312271115';

      2.查询平均时长最大 30条：

     SELECT  t2.rolname, t3.datname,queryid,query,  (total_time/calls) / 1000 as average  

      FROM    pg_stat_statements t1 JOIN pg_roles t2 ON (t1.userid=t2.oid) JOIN pg_database t3 ON (t1.dbid=t3.oid) 

      ORDER   BY average DESC LIMIT 30;