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.,
$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
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:
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.
has two options. The
VERBOSE option provides additional
information like fully qualified table names—
usually not very valuable.
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
select statements but it
modifies your data when using it for
delete. To avoid the risk of accidentally
modifying your data, you can enclose it in a transaction and perform a
explain analyze executes
the explained statement, even if the statement is an
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):
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
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: