Using EXPLAIN

From PostgreSQL wiki
Jump to navigationJump to 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:

  • pgadmin includes a visual EXPLAIN tool that helps map out what's actually happening. See Reading PgAdmin Graphical Explain Plans.
  • Visual Explain, originally a RedHat component that has been kept current and improved by EnterpriseDB, comes bundled with the EnterpriseDB Advanced Server package. It can be built to run against other PostgreSQL installations using the source code to their Developer Studio package.
  • PostgreSQL execution plan visualizer displays a visual tree of the query plan with several details per node (GitHub repository). An older version exists, but does not render plans with parallel workers correctly (blog post, GitHub repository).
  • explain.depesz.com shows explain plan with extracted summarized times, and highlights based on chosen criteria.
  • pgMustard is a commercial tool (with a free trial) which visualizes EXPLAIN output, and gives tips to improve performance.
  • explain-postgresql.com analyzes EXPLAIN plan from PostgreSQL and related (Greenplum, Citus, TimescaleDB and Amazon RedShift). Shows plan and node details and visualizations with piechart, flowchart and tilemap, also gives smart recommendations to improve query. Free for personal use.

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 4MB 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.