0

I'm trying to graph the ouput of pg_statements, so we can see min by min the avg, min and max of time taken for transactions. By default pg_statements appears to just be ever increasing counts.

(Currently stuck on version 9.2)

so ive been trying variations of the below. Essentially, needing a query or function that will output the result and then do the reset command, so we get a fresh minute of data

create or replace function query_time()
returns TABLE(avg double precision, max double precision, min double precision)
language plpgsql as $$
begin
    create temporary table temp_table of query_time on commit drop;
    insert into temp_table
         select 
            avg(total_time/calls) as avg,
            max(total_time/calls) as max,
            min(total_time/calls) as min
          from pg_stat_statements;
     select pg_stat_statements_reset();
    return query select * from temp_table;
end $$;
WITH myResultSet AS 
  ( SELECT  
            avg(total_time/calls) as avg,
            max(total_time/calls) as max,
            min(total_time/calls) as min
          from pg_stat_statements
  ) 

, upd AS
  ( select pg_stat_statements_reset()
  ) 

SELECT *
FROM myResultSet ;


AH101
  • 3
  • 2
  • select avg(total_time/calls) as avg, max(total_time/calls) as max, min(total_time/calls) as min, sum(calls), pg_stat_statements_reset() from pg_stat_statements; – AH101 Jan 11 '23 at 20:59

0 Answers0