I would like to gather statistics on the highest run queries in my database.

For that I need to know how I can track queries so I can create views or materialized views to cache the results of the highest run queries since I have a big DB.

  • 2
    Have you checked here?
    – Nelson
    Jun 9, 2015 at 13:17
  • 2
  • Out of curiosity, how big is big? Also, by 'highest run' do you mean the most often run or the cumulatively most expensive queries? And, with a simple view, you don't spare anything in this regard. Jun 9, 2015 at 13:32
  • @ dezso: it is 300k rows on main table. The most expensive would be a priority but i also want to know the most often run.
    – mamesaye
    Jun 9, 2015 at 13:59
  • As @a_horse_with_no_name already said .. the pg_stat_statements extension is probably what you need. Jun 9, 2015 at 15:38

1 Answer 1


As someone said in the comments, pg_stat_statements is the way to get the statistics. Put this into your postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000
pg_stat_statements.track = all

Then run this query:

CREATE EXTENSION pg_stat_statements; 

After that, this sample query (copied from the docs linked above) will give you the stats for 5 top queries from all the databases:

SELECT query, calls, total_time, rows,
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

If you want results for a single database, you need to filter by dbid which you can get from pg_database by db name. Add this WHERE clause to the query above:

WHERE dbid = (select oid from pg_database where datname = 'YOUR_DB_NAME')

You could also do a join.

When you're testing this it may be a good idea to exclude the queries to the stats/schema tables themselves, for example:

AND query not similar to '%( pg_|information_schema)%'

There's a bunch of free and commercial tools that can help you visualize the data.

  • 3
    Nowadays (postgres 14) there is no total_time in there, but there is total_exec_time
    – Benni
    Jul 13, 2022 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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