How to check/know the highest run queries

Question

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.

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.