Book Image

Troubleshooting PostgreSQL

Book Image

Troubleshooting PostgreSQL

Overview of this book

Table of Contents (17 chapters)
Troubleshooting PostgreSQL
About the Author
About the Reviewers

Chasing down slow queries

After inspecting active queries, checking for I/O problems, and locking, it might be interesting to see which queries are actually causing most of the trouble. Without knowing the actual time-consuming queries, it is pretty hard to improve things in the long run. Fortunately, PostgreSQL provides a module called pg_stat_statements that is capable of tracking queries and providing administrators with statistical information about those queries.

To use this module, it has to be enabled:

test=# CREATE EXTENSION pg_stat_statements;

Note that the module has to be enabled inside a database. The system views created by this module will only live in this database for now.

However, we are not done yet:

test=# SELECT * FROM pg_stat_statements;
ERROR:  pg_stat_statements must be loaded via

The module is actually loaded when the postmaster starts up. It cannot be loaded on the fly because the information created must survive the disconnection...