EXPLAIN() SQL Commands EXPLAIN()NAMEEXPLAIN - Shows statement execution plan
SYNOPSISEXPLAIN [ 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 gen‐
erates for the supplied query. The execution plan shows how the ta‐
ble(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, any‐
way). Also, if you limit the number of tuples to return with a LIMIT
clause, the planner makes an appropriate interpolation between the end‐
point 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 of cost
information in Postgres. General information on 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 improve‐
ments.
COMPATIBILITY
SQL92
There is no EXPLAIN statement defined in SQL92.
SQL - Language Statements 29 March 2001 EXPLAIN()