RedHat 9 (Linux i386) - man page for explain (redhat section 7)

Linux & Unix Commands - Search Man Pages

Man Page or Keyword Search:   man
Select Man Page Set:       apropos Keyword Search (sections above)


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 (formatted 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 table(s) referenced by the query will  be
       scanned---by  plain  sequential scan, index scan, etc.---and if multiple tables are refer-
       enced, 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 plan-
       ner's estimates 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
       configuration parameter.

   NOTES
       There  is  only	sparse	documentation on the optimizer's use of cost information in Post-
       greSQL.	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 improvements.

COMPATIBILITY
   SQL92
       There is no EXPLAIN statement defined in SQL92.

SQL - Language Statements		    2002-11-22				       EXPLAIN(7)
Unix & Linux Commands & Man Pages : ©2000 - 2018 Unix and Linux Forums


All times are GMT -4. The time now is 03:21 PM.

Unix & Linux Forums Content Copyright©1993-2018. All Rights Reserved.
×
UNIX.COM Login
Username:
Password:  
Show Password





Not a Forum Member?
Forgot Password?