by Markus Winand.

Getting an 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:


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.


Statements without bind parameters can be explained directly:


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.

On my Own Behalf

I make my living from training, other SQL related services and selling my book. Learn more at

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.


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):

                   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

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:


About the Author

Photo of Markus Winand

Markus Winand is the SQL Renaissance Ambassador. He is on a mission to introduce developers to the evolution of SQL in the 21st century. Markus can be hired as trainer, speaker and consultant via

Buy his Book on Amazon

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy on Amazon
(paperback only)

Paperback and PDF also available at Markus’ store.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of any size.
Learn more »

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

The Use The Index, Luke! mug

Stickers, coasters, books and coffee mugs. All you need for learning.

Shop now

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license