explain man page on Scientific

Man page or keyword search:  
man Server   26626 pages
apropos Keyword Search (all sections)
Output format
Scientific logo
[printable version]

EXPLAIN(7)			 SQL Commands			    EXPLAIN(7)

NAME
       EXPLAIN - show the execution plan of a statement

SYNOPSIS
       EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

DESCRIPTION
       This  command  displays	the execution plan that the PostgreSQL planner
       generates for the supplied statement. The execution plan shows how  the
       table(s) referenced by the statement will be scanned — by plain sequen‐
       tial scan, index scan, etc. — and if multiple  tables  are  referenced,
       what  join  algorithms will be used to bring together the required rows
       from each input table.

       The most critical part of the display is the estimated statement execu‐
       tion cost, which is the planner's guess at how long it will take to run
       the statement (measured in units of disk page  fetches).	 Actually  two
       numbers	are  shown:  the  start-up  time  before  the first row can be
       returned, and the total time to return all the rows. For	 most  queries
       the  total  time is what matters, but in contexts such as a subquery in
       EXISTS, the planner will choose the smallest start-up time  instead  of
       the smallest total time (since the executor will stop after getting one
       row, anyway).  Also, if you limit the number of rows 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 statement 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.

	      Important: Keep in mind that the statement is actually  executed
	      when  the	 ANALYZE option is used. Although EXPLAIN will discard
	      any output that a SELECT would return, other side effects of the
	      statement	 will happen as usual. If you wish to use EXPLAIN ANA‐
	      LYZE on an INSERT, UPDATE, DELETE, CREATE TABLE AS,  or  EXECUTE
	      statement without letting the command affect your data, use this
	      approach:

	      BEGIN;
	      EXPLAIN ANALYZE ...;
	      ROLLBACK;

PARAMETERS
       ANALYZE
	      Carry out the command and show the actual run times.

       VERBOSE
	      Include the output column list for each node in the plan tree.

       statement
	      Any SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, or
	      CREATE TABLE AS statement, whose execution plan you wish to see.

NOTES
       There  is  only	sparse	documentation  on  the optimizer's use of cost
       information in PostgreSQL. Refer	 to  in	 the  documentation  for  more
       information.

       In  order  to  allow  the  PostgreSQL  query planner to make reasonably
       informed decisions when optimizing queries,  the	 ANALYZE  [analyze(7)]
       statement  should be run to record statistics about the distribution of
       data within the table. If you have not done this (or if the statistical
       distribution  of	 the data in the table has changed significantly since
       the last time ANALYZE was run), the estimated  costs  are  unlikely  to
       conform	to the real properties of the query, and consequently an infe‐
       rior query plan might be chosen.

       Genetic query  optimization  (GEQO)  randomly  tests  execution	plans.
       Therefore,  when	 the  number  of join relations exceeds geqo_threshold
       causing genetic query optimization to be used, the  execution  plan  is
       likely to change each time the statement is executed.

       In  order  to  measure  the run-time cost of each node in the execution
       plan, the current implementation of EXPLAIN ANALYZE can	add  consider‐
       able  profiling	overhead  to  query  execution.	 As  a result, running
       EXPLAIN ANALYZE on a query can sometimes take significantly longer than
       executing  the  query  normally.	 The amount of overhead depends on the
       nature of the query.

EXAMPLES
       To show the plan for a simple query on a table with  a  single  integer
       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 condi‐
       tion, EXPLAIN might 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)

       Here is an example of a query plan for a query using an aggregate func‐
       tion:

       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)

       Here  is	 an  example of using EXPLAIN EXECUTE to display the execution
       plan for a prepared query:

       PREPARE query(int, int) AS SELECT sum(bar) FROM test
	   WHERE id > $1 AND id < $2
	   GROUP BY foo;

       EXPLAIN ANALYZE EXECUTE query(100, 200);

							      QUERY PLAN
       -------------------------------------------------------------------------------------------------------------------------
	HashAggregate  (cost=39.53..39.53 rows=1 width=8) (actual time=0.661..0.672 rows=7 loops=1)
	  ->  Index Scan using test_pkey on test  (cost=0.00..32.97 rows=1311 width=8) (actual time=0.050..0.395 rows=99 loops=1)
		Index Cond: ((id > $1) AND (id < $2))
	Total runtime: 0.851 ms
       (4 rows)

       Of course, the specific numbers shown here depend on  the  actual  con‐
       tents  of the tables involved. Also note that the numbers, and even the
       selected query strategy, might vary between PostgreSQL releases due  to
       planner improvements. In addition, the ANALYZE command uses random sam‐
       pling to estimate data statistics; therefore, it is possible  for  cost
       estimates  to  change  after a fresh run of ANALYZE, even if the actual
       distribution of data in the table has not changed.

COMPATIBILITY
       There is no EXPLAIN statement defined in the SQL standard.

SEE ALSO
       ANALYZE [analyze(7)]

SQL - Language Statements	  2013-10-08			    EXPLAIN(7)
[top]

List of man pages available for Scientific

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net