Analyzing the real-time performance of your queries

This recipe is mainly for users of PostgreSQL 9.2 and above, and it is about the pg_stat_statements extension. Even though this extension is available in PostgreSQL 8.4, it lacks a crucial feature—query normalization.

The pg_stat_statements extension adds the capability to track execution statistics of queries that are run in a database, including the number of calls, total execution time, total number of returned rows, as well as internal information on memory and I/O access.

It is evident how this approach opens up new opportunities in PostgreSQL performance analysis—by allowing DBAs to get insights directly from the database, through SQL and in real time.

Getting ready

The pg_stat_statements module is available as a contrib module of PostgreSQL. The extension must be installed as a superuser in the desired databases. It also requires administrators to add the library in the postgresql.conf file, as follows:

shared_preload_libraries = 'pg_stat_statements...