PostgreSQL 11 Administration Cookbook

Finding slow SQL statements

There are two main kinds of slowness that can manifest themselves in a database.

The first kind is a single query that can be too slow to be really usable, such as a customer information query in a CRM running for minutes, a password check query running in tens of seconds, or a daily data aggregation query running for more than a day. These can be found by logging queries that take over a certain amount of time, either at the client end or in the database.

The second kind is a query that is run frequently (say a few thousand times a second) and used to run in single-digit milliseconds, but is now running in several tens or even hundreds of milliseconds, hence slowing the system down. This kind of slowness is much ...