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 ;