Get statistics of slow running queries

Question

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?

Answer 1

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 ..

Answer 2

How @JoishiBodio said you can use pg_stat_statements extension to see slow queries statistics. For those who struggle with installation (as I did):

1. Check if pg_stat_statements is in list of available extensions:

   SELECT * FROM pg_available_extensions;
   

2. Try installing postgresql-contrib package via your system package manager, on Debian/Ubuntu:

   sudo apt-get install postgresql-contrib-9.5
   

Then do step 1 again.

3. Add pg_stat_statements to shared_preload_libraries in PostgreSQL configuration:

via postgres.conf:

    shared_preload_libraries = 'pg_stat_statements'

via command line arguments (no quotes!):

    postgres -c shared_preload_libraries=pg_stat_statements

4. Then create the extension in a database:

   CREATE EXTENSION pg_stat_statements;