Closed unhandled-exception closed 2 years ago
I think we need there not only column _exec_time, but and _plan_time
Example
select sum(calls) as calls, sum(total_exec_time + total_plan_time) as total_time, sum((mean_exec_time + mean_plan_time) * calls) / sum(calls) as mean_time, max(max_exec_time + max_plan_time) as max_time, min(min_exec_time + min_plan_time) as min_time, -- stddev_time, -- https://stats.stackexchange.com/questions/55999/is-it-possible-to-find-the-combined-standard-deviation sum(rows) as rows, (select usename from pg_user where usesysid = userid) as usr, (select datname from pg_database where oid = dbid) as db, query, sum(shared_blks_hit) as shared_blks_hit, sum(shared_blks_read) as shared_blks_read, sum(shared_blks_dirtied) as shared_blks_dirtied, sum(shared_blks_written) as shared_blks_written, sum(local_blks_hit) as local_blks_hit, sum(local_blks_read) as local_blks_read, sum(local_blks_dirtied) as local_blks_dirtied, sum(local_blks_written) as local_blks_written, sum(temp_blks_read) as temp_blks_read, sum(temp_blks_written) as temp_blks_written, sum(blk_read_time) as blk_read_time, sum(blk_write_time) as blk_write_time, array_agg(queryid) as queryids -- 9.4+ from pg_stat_statements group by userid, dbid, query order by sum(total_exec_time + total_plan_time) desc limit 50;
but query sometimes not work, because in new postgres calls can be zero.
select sum(calls) as calls, sum(total_exec_time + total_plan_time) as total_time, sum((mean_exec_time + mean_plan_time) * calls) / greatest(sum(calls),1) as mean_time, max(max_exec_time + max_plan_time) as max_time, min(min_exec_time + min_plan_time) as min_time, -- stddev_time, -- https://stats.stackexchange.com/questions/55999/is-it-possible-to-find-the-combined-standard-deviation sum(rows) as rows, (select usename from pg_user where usesysid = userid) as usr, (select datname from pg_database where oid = dbid) as db, query, sum(shared_blks_hit) as shared_blks_hit, sum(shared_blks_read) as shared_blks_read, sum(shared_blks_dirtied) as shared_blks_dirtied, sum(shared_blks_written) as shared_blks_written, sum(local_blks_hit) as local_blks_hit, sum(local_blks_read) as local_blks_read, sum(local_blks_dirtied) as local_blks_dirtied, sum(local_blks_written) as local_blks_written, sum(temp_blks_read) as temp_blks_read, sum(temp_blks_written) as temp_blks_written, sum(blk_read_time) as blk_read_time, sum(blk_write_time) as blk_write_time, array_agg(queryid) as queryids -- 9.4+ from pg_stat_statements group by userid, dbid, query order by sum(total_exec_time + total_plan_time) desc limit 50;
In Postgres 13, the column names in pg_stat_statements have changed. I propose a change that adds version checking for s1 and s2 actions.