Mastering PostgreSQL 12 - Third Edition

Using pg_stat_statements

Now that we've discussed the first couple of views, it's time to turn our attention to one of the most important views, which can be used to spot performance problems. I am, of course, speaking about pg_stat_statements. The idea is to have information about queries on your system. It helps us figure out which types of queries are slow and how often queries are called.

To use this module, we need to follow three steps:

1. Add pg_stat_statements to shared_preload_libraries in the postgresql.conf file.
2. Restart the database server.
3. Run CREATE EXTENSION pg_stat_statements in the databases of your choice.

Let's inspect the definition of the view:

test=# \d pg_stat_statements                     View "public.pg_stat_statements" Column | Type ...