8

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.

  • 1
    Have you checked here? – Nelz Jun 9 '15 at 13:17
  • 1
  • 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. – dezso Jun 9 '15 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 '15 at 13:59
  • As @a_horse_with_no_name already said .. the pg_stat_statements extension is probably what you need. – Joishi Bodio Jun 9 '15 at 15:38
7

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.

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.