How to do it…

The core issues are likely to be one of these:

  • You're asking it to do too much work
  • Something is stopping it from doing the work

This might not sound that helpful at first, but it's good to know that there's nothing really magical going on that you can't understand if you look.

In more detail, the main reasons are:

  • Returning too much data
  • Processing too much data index needed
  • Wrong plan for other reasons
  • Cache or I/O problems
  • Locking problems

The first reason can be handled as described in the Reducing the number of rows returned recipe. Reasons 2-4 can be investigated from two perspectives: The SQL itself and the objects that the SQL touches. Let's start by looking at the SQL itself by running the query with EXPLAIN ANALYZE ...

Get PostgreSQL 10 Administration Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.