EXPLAIN

Name

EXPLAIN -- show the execution plan of a
statement

Synopsis

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

Description

This command displays the execution plan that the PostgreSQL planner generates for the
supplied statement. The execution plan shows how the table(s)
referenced by the statement will be scanned — by plain sequential
scan, index scan, etc. — and if multiple tables are referenced,
what join algorithms will be used to bring together the required
rows from each input table.

The most critical part of the display is the estimated
statement execution cost, which is the planner's guess at how
long it will take to run the statement (measured in units of disk
page fetches). Actually two numbers are shown: the start-up time
before the first row can be returned, and the total time to
return all the rows. For most queries the total time is what
matters, but in contexts such as a subquery in EXISTS, the planner will choose the smallest
start-up time instead of the smallest total time (since the
executor will stop after getting one row, anyway). Also, if you
limit the number of rows to return with a LIMIT clause, the planner makes an appropriate
interpolation between the endpoint costs to estimate which plan
is really the cheapest.

The ANALYZE option causes the
statement to be actually executed, not only planned. The total
elapsed time expended within each plan node (in milliseconds) and
total number of rows it actually returned are added to the
display. This is useful for seeing whether the planner's
estimates are close to reality.

Important: Keep in mind that the statement is
actually executed when the ANALYZE
option is used. Although EXPLAIN
will discard any output that a SELECT would return, other side effects of the
statement will happen as usual. If you wish to use EXPLAIN ANALYZE on an INSERT, UPDATE,
DELETE, CREATE
TABLE AS, or EXECUTE statement
without letting the command affect your data, use this
approach:

Notes

There is only sparse documentation on the optimizer's use of
cost information in PostgreSQL.
Refer to Section 14.1 for more
information.

In order to allow the PostgreSQL query planner to make reasonably
informed decisions when optimizing queries, the ANALYZE statement should be run to
record statistics about the distribution of data within the
table. If you have not done this (or if the statistical
distribution of the data in the table has changed significantly
since the last time ANALYZE was run),
the estimated costs are unlikely to conform to the real
properties of the query, and consequently an inferior query plan
might be chosen.

Genetic query optimization (GEQO) randomly tests execution plans.
Therefore, when the number of join relations exceeds geqo_threshold
causing genetic query optimization to be used, the execution plan
is likely to change each time the statement is executed.

In order to measure the run-time cost of each node in the
execution plan, the current implementation of EXPLAIN ANALYZE can add considerable profiling
overhead to query execution. As a result, running EXPLAIN ANALYZE on a query can sometimes take
significantly longer than executing the query normally. The
amount of overhead depends on the nature of the query.

Examples

To show the plan for a simple query on a table with a single
integer column and 10000 rows:

Of course, the specific numbers shown here depend on the
actual contents of the tables involved. Also note that the
numbers, and even the selected query strategy, might vary between
PostgreSQL releases due to
planner improvements. In addition, the ANALYZE command uses random sampling to estimate
data statistics; therefore, it is possible for cost estimates to
change after a fresh run of ANALYZE,
even if the actual distribution of data in the table has not
changed.