EXPLAIN - Shows statement execution plan
SYNOPSIS
EXPLAIN [ VERBOSE ] query
INPUTS
VERBOSE
Flag to show detailed query plan.
query Any query.
OUTPUTS
NOTICE: QUERY PLAN:
Explicit query plan from the Postgres backend.
EXPLAIN
Flag sent after query plan is shown.
DESCRIPTION
This command displays the execution plan that the Postgres
planner generates for the supplied query. The execution
plan shows how the table(s) referenced by the query 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
tuples from each input table.
The most critical part of the display is the estimated
query execution cost, which is the planner's guess at how
long it will take to run the query (measured in units of
disk page fetches). Actually two numbers are shown: the
start-up time before the first tuple can be returned, and
the total time to return all the tuples. For most queries
the total time is what matters, but in contexts such as an
EXISTS sub-query the planner will choose the smallest
start-up time instead of the smallest total time (since
the executor will stop after getting one tuple, anyway).
Also, if you limit the number of tuples to return with a
LIMIT clause, the planner makes an appropriate interpola
tion between the endpoint costs to estimate which plan is
really the cheapest.
The VERBOSE option emits the full internal representation
of the plan tree, rather than just a summary (and sends it
to the postmaster log file, too). Usually this option is
only useful for debugging Postgres.
NOTES
There is only sparse documentation on the optimizer's use
cost estimation for query optimization can be found in
database textbooks. Refer to the Programmer's Guide in
the chapters on indexes and the genetic query optimizer
for more information.
USAGE
To show a query plan for a simple query on a table with a
single int4 column and 128 rows:
EXPLAIN SELECT * FROM foo;
NOTICE: QUERY PLAN:
Seq Scan on foo (cost=0.00..2.28 rows=128 width=4)
EXPLAIN
For the same table with an index to support an equijoin
condition on the query, EXPLAIN will show a different
plan:
EXPLAIN SELECT * FROM foo WHERE i = 4;
NOTICE: QUERY PLAN:
Index Scan using fi on foo (cost=0.00..0.42 rows=1 width=4)
EXPLAIN
And finally, for the same table with an index to support
an equijoin condition on the query, EXPLAIN will show the
following for a query using an aggregate function:
EXPLAIN SELECT sum(i) FROM foo WHERE i = 4;
NOTICE: QUERY PLAN:
Aggregate (cost=0.42..0.42 rows=1 width=4)
-> Index Scan using fi on foo (cost=0.00..0.42 rows=1 width=4)
Note that the specific numbers shown, and even the
selected query strategy, may vary between Postgres
releases due to planner improvements.
COMPATIBILITY
SQL92
There is no EXPLAIN statement defined in SQL92.
Man(1) output converted with
man2html