Getting a PostgreSQL Execution Plan


A PostgreSQL execution plan is fetched by putting the explain command in front of an SQL statement. There is, however, one important limitation: SQL statements with bind parameters (e.g., $1, $2, etc.) cannot be explained this way—they need to be prepared first:

PREPARE stmt(int) AS SELECT $1

Note that PostgreSQL uses "$n" for bind parameters. Your database abstraction layer might hide this so you can use question marks as defined by the SQL standard.

The execution of the prepared statement can be explained:

EXPLAIN EXECUTE stmt(1)

Up till PostgreSQL 9.1, the execution plan was already created with the prepare call and could therefore not consider the actual values provided with execute. Since PostgreSQL 9.2 the creation of the execution plan is postponed until execution and thus can consider the actual values for the bind parameters.

Note

Statements without bind parameters can be explained directly:

EXPLAIN SELECT 1

In this case, the optimizer has always considered the actual values during query planning. If you use PostgreSQL 9.1 or earlier and bind parameters in your program, you should also use explain with bind parameters to retrieve the same execution plan.

The explain plan output is as follows:

                QUERY PLAN                
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)

The output has similar information as the Oracle execution plans shown throughout the book: the operation name (“Result”), the related cost, the row count estimate, and the expected row width.

Note that PostgreSQL shows two cost values. The first is the cost for the startup, the second is the total cost for the execution if all rows are retrieved. The Oracle database’s execution plan only shows the second value.

The PostgreSQL explain command has two options. The VERBOSE option provides additional information like fully qualified table names—VERBOSE is usually not very valuable.

It is well written and is not too heavy on the guts of databases
Mr. B on Amazon.co.uk (5 stars)

The second explain option is ANALYZE. Although it is widely used, I recommend not getting into the habit of using it automatically because it actually executes the statement. That is mostly harmless for select statements but it modifies your data when using it for insert, update or delete. To avoid the risk of accidentally modifying your data, you can enclose it in a transaction and perform a rollback afterwards.

Warning

explain analyze executes the explained statement, even if the statement is an insert, update or delete.

The ANALYZE option executes the statement and records actual timing and row counts. That is valuable in finding the cause of incorrect cardinality estimates (row count estimates):

BEGIN;
EXPLAIN ANALYZE EXECUTE stmt(1);
                   QUERY PLAN
--------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)
         (actual time=0.002..0.002 rows=1 loops=1)
 Total runtime: 0.020 ms;
ROLLBACK;

Note that the plan is formatted for a better fit on the page. PostgreSQL prints the “actual” values on the same line as the estimated values.

The row count is the only value that is shown in both parts—in the estimated and in the actual figures. That allows you to quickly find erroneous cardinality estimates.

Last but not least, prepared statements must be closed again:

DEALLOCATE stmt

About the Author

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/