How to characterize query statements using pg_stat_statements.
The GUC pg_stat_statements.track can be set to all to capture nested statements and statements invoked within functions. Otherwise the default values of top will only capture statements issued directly by clients.
The extension needs to be loaded per database:
CREATE EXTENSION pg_stat_statements;This will created a view called pg_stat_statements and provide queries details about frequency, execution time, cached hits, i/o request, and temporary space usage.
This is a very simple example for querying pg_stat_statements to determine which queries are taking up the most time.
SELECT total_time, query
FROM pg_stat_statements
ORDER BY total_time DESC;It may make sense to clear the data collection to gather fresh data at times:
SELECT pg_stat_statements_reset();