Postgresql – Get statistics of slow running queries

performancepostgresql

My database application runs many different queries. I have set log_min_duration_statement to 1000. But queries logged there are not always slow, most times they just take a few ms.

Is it possible to get a statistic of all queries, and how often they took longer than 1000ms?

Best Answer

Install the extension pg_stat_statements with the SQL command

CREATE EXTENSION pg_stat_statements

You may want to make sure you create this by using an appropriate user (such as the user your application uses or some dba account). Be aware that whichever user creates the extension will also own it.

This will require a server restart for it to be usable (because you have to change the config in postgresql.conf a tiny bit that affects memory consumption), but it will give you statistics on all queries.

Please see the documentation page for more information about what pg_stat_statements provides. See here for a quick reference on configuration of the postgresql.conf config file for pg_stat_statements.

Once you have it installed and running, you can view statistics by querying the pg_stat_statements view..

select * from pg_stat_statements;
-- or
select * from pg_stat_statements where total_time / calls > 200; -- etc ..