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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
As someone said in the comments, pg_stat_statements is the way to get the statistics. Put this into your
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.