2

The data in the pg_stat_statements table is really useful for me. I need to know the queries and the time they take. How can I perform a query to pg_stat_statements to get the "total_time" over the last 2 hours? I see no column for this.

2

If you are interested in a two hour period, you can collect the aggregated data, save it with a insert statement and then call pg_stat_statements_reset() for refreshing the data.

SELECT 
  query,
  SUM(total_time) as total_sum,
  (total_time / 1000 / 60) as total_minutes, 
  (total_time/calls) as average_time
FROM pg_stat_statements 
ORDER BY 1 DESC;

Maybe you can also check The Statistics Collector to extend your data.

| improve this answer | |
  • I think SUM isn't needed? select query, total_time, ... would be the same? — There's no group by. – KajMagnus Feb 17 at 8:31
2

I don't know of a canned tool to do this. But you can easily create a table which has the same columns as pg_stat_statements and an extra timestamptz column, then have a scheduled task take a snapshot of pg_stat_statements into this table every 2 hours. Then you can take the differences between adjacent snapshots of the same query.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.