Simple pg_stat_statements snapshots

dba_pgss.sql

create schema dba;

-- on GCP's Cloud SQL for Postgres, if we work with more than one DB user,
-- we have a problem – some queries are not visible (`<insufficient privilege>`),
-- so we need to use a workaround
create or replace function dba.pgss_snapshot() returns setof pg_stat_statements as $$
declare
  rec pg_stat_statements;
  _role text;
begin
  foreach _role in array array[
    'postgres', ... -- list your DB users here
  ] loop
    execute format ('set role to %s;', _role);
    for rec in select * from pg_stat_statements where not query ~ 'insufficient privilege' loop
      return next rec;
    end loop;
  end loop;
end;
$$ language plpgsql;

create table dba.pgss as select now(), * from dba.pgss_snapshot();

-- wait some time
-- ...
-- make another snap

insert into dba.pgss select now(), * from dba.pgss_snapshot();


-- analyze two latest snapshots
with snaps(last_snap, prev_snap) as (
  select
    (select now from dba.pgss group by now order by now desc limit 1),
    (select now from dba.pgss group by now order by now desc limit 1 offset 1)
), delta as (
  select
    query,
    max(calls) - min(calls) as calls,
    max(rows) - min(rows) as rows,
    max(total_time) - min(total_time) as total_time,
    max(shared_blks_hit) - min(shared_blks_hit) as shared_blks_hit,
    max(shared_blks_read) - min(shared_blks_read) as shared_blks_read,
    max(shared_blks_dirtied) - min(shared_blks_dirtied) as shared_blks_dirtied,
    max(shared_blks_written) - min(shared_blks_written) as shared_blks_written
  from dba.pgss
  where now in (select last_snap from snaps union all select prev_snap from snaps)
  group by 1
)
select
  row_number() over (order by total_time desc) as pos,
  calls,
  rows,
  round(total_time::numeric, 2) as total__time,
  (select last_snap::text from snaps) as last_snapshot,
  (select extract('epoch' from last_snap) - extract('epoch' from prev_snap) from snaps) as delta_seconds,
  round(total_time::numeric / nullif(calls, 0), 2) as avg_total,
  shared_blks_hit as sh_b_hit,
  shared_blks_read as sh_b_read,
  shared_blks_dirtied as sh_b_dirt,
  shared_blks_written as sh_b_writ,
  round(shared_blks_hit::numeric / nullif(calls, 0), 2) as avg_sh_b_hit,
  round(shared_blks_read::numeric / nullif(calls, 0), 2) as avg_sh_b_read,
  round(shared_blks_dirtied::numeric / nullif(calls, 0), 2) as avg_sh_b_dirt,
  round(shared_blks_written::numeric / nullif(calls, 0), 2) as avg_sh_b_writ,
  round(calls::numeric / (select extract('epoch' from last_snap) - extract('epoch' from prev_snap) from snaps)::numeric, 2) as calls_per_sec,
  round(rows::numeric / (select extract('epoch' from last_snap) - extract('epoch' from prev_snap) from snaps)::numeric, 2) as rows_per_sec,
  round(total_time::numeric / (select extract('epoch' from last_snap) - extract('epoch' from prev_snap) from snaps)::numeric, 2) as total_time_per_sec,
  query
from delta
order by total_time desc
limit 50;

I keep getting ERROR: division by zero