Tuesday, December 12, 2017

PostgreSQL: Identify your slowest queries

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:

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:

No comments: