Explain Plan

Postgres has a great ability to show you how it will actually execute a query under the covers. This is known as an execution plan, and it is exposed by the explain command. Understanding this tells you how you can optimize your database with indexes to improve performance.

Every query within Postgres has an execution plan when executed. There are three ways to run explain to expose this to you:

  • The generic form (only shows what is likely to happen)
  • The Analyze form (which actually runs the query, and outputs what does happen)
  • The Verbose form (verbose information)

Most commonly, explain is run on the SELECT statements. However, you can also use it on the following:

  • INSERT
  • UPDATE
  • DELETE
  • EXECUTE
  • DECLARE

Generating and reading the Explain Plan ...

Get PostgreSQL Development Essentials 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.

Start your free trial