EXPLAIN(7)							   SQL Commands 							EXPLAIN(7)

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 run times.

       VERBOSE
	      Flag to show detailed query plan dump.

       query  Any query.

   OUTPUTS
       Query plan
	      Explicit query plan from the PostgreSQL planner.

	      Note: Prior to PostgreSQL 7.3, the query plan was emitted in the form of a NOTICE message. Now it appears as a query result (format-
	      ted like a table with a single text column).

DESCRIPTION

       This command displays the execution plan that the PostgreSQL planner generates 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, 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 esti-
       mates are close to reality.

	      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;

       The VERBOSE option emits the full internal representation of the plan tree, rather than just a summary.	Usually this option is only useful
       for debugging PostgreSQL. The VERBOSE dump is either pretty-printed or not, depending on the setting of the EXPLAIN_PRETTY_PRINT configura-
       tion parameter.

   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 10000 rows:

       EXPLAIN SELECT * FROM foo;
				  QUERY PLAN
       ---------------------------------------------------------
	Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
       (1 row)

       If there is an index and we use a query with an indexable WHERE condition, EXPLAIN will show a different plan:

       EXPLAIN SELECT * FROM foo WHERE i = 4;
				    QUERY PLAN
       --------------------------------------------------------------
	Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)
	  Index Cond: (i = 4)
       (2 rows)

       And here is an example of a query plan for a query using an aggregate function:

       EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;
					QUERY PLAN
       ---------------------------------------------------------------------
	Aggregate  (cost=23.93..23.93 rows=1 width=4)
	  ->  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)
		Index Cond: (i < 10)
       (3 rows)

       Note that the specific numbers shown, and even the selected query strategy, may vary between PostgreSQL releases due  to  planner  improve-
       ments.

COMPATIBILITY

   SQL92
       There is no EXPLAIN statement defined in SQL92.

SQL - Language Statements					    2002-11-22								EXPLAIN(7)