PostgreSQL provides a large list of modules that extends its core functionality. One of these modules is the pg_stat_statements module that provides a means for tracking execution statistics of all SQL statements executed by a server.
Before you can use this module, it must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql.conf, because it requires additional shared memory. This means that a server restart is needed to add the module.
After loading the module you can execute the below query to get the top 5 duration queries executed during your performance/benchmarking run:
| 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 |
It is recommended to reset the pg_stat_statements using the query below to ensure that you only capture the statements from your performance/benchmarking run:
| SELECT pg_stat_statements_reset() |