Detecting slow queries

Now that we have seen how to hunt down tables that might need an index, we can move on to the next example and try to figure out the queries that cause most of the load on your system. Sometimes, the slowest query is not the one causing a problem; it is a bunch of small queries, which are executed over and over again. In this section, you will learn how to track down such queries.

To track down slow operations, we can rely on a module called pg_stat_statements. This module is available as part of the PostgreSQL contrib section. Installing a module from this section is really easy. Connect to PostgreSQL as a superuser, and execute the following instruction (if contrib packages have been installed):

test=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

This module will install a system view that will contain all the relevant information we need to find expensive operations:

test=# \d pg_stat_statements
          View "public.pg_stat_statements"
      ...