From PostgreSQL wiki
Jump to: navigation, search

Figuring out why a statement is taking so long to execute is done with the EXPLAIN command. You can run EXPLAIN multiple ways; if you use EXPLAIN ANALYZE, it will actually run the statement and let you compare what the planner thought was going to happen with what actually did. Note that if the statement changes data, that will also happen when you run with EXPLAIN ANALYZE; if you just use EXPLAIN the statement doesn't do anything to the database.

There are some tools available to help interpret EXPLAIN output:

And there are a few tutorials on this subject, many of which are titled "Explaining Explain". While many deal with direct fundamentals, they are listed here chronologically:

There is also a section in the online documentation that discusses performance tips:

A common problem that causes the planner to make bad decisions is not keeping Planner Statistics updated. Another is leaving the tuning parameters that let the server know how memory is available at the very small defaults. For example, in the stock configuration, sorts that take more than 1MB are swapped to disk as being too big to process in memory. Tuning Your PostgreSQL Server covers good practices for sizing the memory and other tuning parameters that most impact query planning.

One thing you do when stumped by a plan is to submit the full EXPLAIN ANALYZE output, along with the schema of the involved queries, to the pgsql-performance mailing list. To get a useful reply more quickly, please read SlowQueryQuestions before posting. Note that if you're not running a relatively current version of PostgreSQL, it's quite possible the answer you'll get is that the problem is resolved in a later one. It may save you some time to try at least the most current update to the version you're using (i.e. upgrade to 11.6 if that's the current latest rev and you're using 11.3) and see if that improves the plan you get.

An advanced technique here is to save your explain plans over time and see how they change as the amount of data in the table grows. This can give you an idea if you're collecting statistics aggressively enough. A simple pl/pgsql example is at generic options for explain.