The EXPLAIN query plan - AWS Prescriptive Guidance
The EXPLAIN statementUsing EXPLAIN ANALYZEHow to read the EXPLAIN query plan

The EXPLAIN query plan

PostgreSQL provides the EXPLAIN and EXPLAIN ANALYZE options for returning query plans with details about how the query will be run.

The EXPLAIN statement

The EXPLAIN statement returns the query plan that the PostgreSQL planner generates for a given statement. The query plan shows the following:

  • How tables involved in a statement will be scanned (for example, by index scan or sequential scan)

  • How multiple tables will be joined (for example, hash join, merge join, or nested loop join)

Understanding the plan is critical when improving the performance of the query. After you understand the plan, you can focus on where the query is taking too long and take action to reduce the time.

Using EXPLAIN ANALYZE

In PostgreSQL, EXPLAIN will only generate a plan for the given statement. If you add the ANALYZE keyword, EXPLAIN will return the plan, run the query, and show the actual runtime and row count for each step. This is indispensable for analyzing the query performance.

Important

When using EXPLAIN ANALYZE, be careful with INSERT, UPDATE, and DELETE.

How to read the EXPLAIN query plan

A PostgreSQL query plan is a tree structure consisting of several nodes. The EXPLAIN query plan shows the steps that the database engine uses to run a query. The query plan provides the following information:

  • The type of operations performed, such as sequential scans, index scans, or nested loop joins.

  • A label, such as Seq Scan, Index Scan, or Nested Loop, to describe the operation being performed.

  • The name of the table or index being processed by the query.

  • Cost and row columns with information about the estimated cost in an arbitrary unit of computation and the number of rows processed.

  • The filter condition of any filter applied on the operation, such as the where condition.

  • A visual representation of the steps, with each operation shown as a node and arrows connecting the operations. The order of the operations is shown from left to right, with earlier operations feeding into later operations.

The following screenshot shows the query plan for a sequential scan.

Query plan shown on the Data Output tab in pgAdmin.

The cost estimate (cost=0.00..32.60 rows=2260 width=8) means that PostgreSQL expects that the query will require 32.60 units of computation to return results.

The 0.00 value is the cost at which this node can begin working (in this case, startup time for the query). The rows value is the estimated number of rows that the sequential scan will return. The width value is the estimated size in bytes of the returned rows.

Because the example shows EXPLAIN with the ANALYZE option, the query was run, and the timing information was captured. The result (actual time=0.120..0.121 rows=1 loops=1) means the following:

  • The sequential scan was run one time (the loops value).

  • The scan returned one row.

  • The actual time was 0.12 milliseconds.