Index for Section 5

Alphabetical listing for E

Bottom of page

EXPLAIN(5)
NAME
  EXPLAIN - show the execution plan of a statement

SYNOPSIS
  EXPLAIN [ ANALYZE ] [ VERBOSE ] query

  INPUTS

  ANALYZE
       Flag to carry out the query and show actual runtimes.

  VERBOSE
       Flag to show detailed query plan.

  query
       Any query.

  OUTPUTS

  NOTICE: QUERY PLAN:
       Explicit query plan from the PostgreSQL backend.

  EXPLAIN
       Flag sent after query plan is shown.

DESCRIPTION
  This command displays the execution plan that the PostgreSQL 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 interpolation between the endpoint costs to estimate which
  plan is really the cheapest.

  The ANALYZE option causes the query 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.

  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 PostgreSQL.

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

       BEGIN;
       EXPLAIN ANALYZE ...;
       ROLLBACK;

  NOTES

  There is only sparse documentation on the optimizer's use of cost
  information in PostgreSQL.  Refer to the User's Guide and Programmer's
  Guide 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 PostgreSQL releases due to planner improvements.

COMPATIBILITY
  SQL92

  There is no EXPLAIN statement defined in SQL92.

Index for Section 5

Alphabetical listing for E

Top of page