The pg_stat_statements extension exposes a lot of query statistics that will be helpful in finding slow queries that need to be optimized. In this recipe, we shall see some example queries to view some information from pg_stat_statements.
Getting ready
In order to run the views or queries discussed in this recipe, we should have pg_stat_statements successfully configured and created as an extension. Please note that every query for which the statistics are collected by pg_stat_statements can be uniquely identified through queryid. A hash is generated based on the query text and is visible as queryid when we query the pg_stat_statements view.
How to do it...
In the following commands, we have used a function to trim the query up to just 40 characters for better visibility. We may use an expanded display using \x and avoid truncating the query when viewing the statistics:
- Find the queries that are writing to temp the most:
select queryid, left(query...
