2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v 1.9 2000/02/15 23:37:49 tgl Exp $
6 <refentry id="SQL-EXPLAIN">
8 <refentrytitle id="SQL-EXPLAIN-TITLE">
11 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 Shows statement execution plan
24 <date>1999-07-20</date>
27 EXPLAIN [ VERBOSE ] <replaceable class="PARAMETER">query</replaceable>
30 <refsect2 id="R2-SQL-EXPLAIN-1">
32 <date>1998-09-01</date>
44 Flag to show detailed query plan.
49 <term><replaceable class="PARAMETER">query</replaceable></term>
52 Any <replaceable class="PARAMETER">query</replaceable>.
60 <refsect2 id="R2-SQL-EXPLAIN-2">
62 <date>1998-04-15</date>
71 <term><computeroutput>
73 <replaceable>plan</replaceable>
74 </computeroutput></term>
77 Explicit query plan from the <productname>Postgres</productname> backend.
82 <term><computeroutput>
84 </computeroutput></term>
87 Flag sent after query plan is shown.
96 <refsect1 id="R1-SQL-EXPLAIN-1">
98 <date>1998-04-15</date>
105 This command displays the execution plan that the Postgres planner
106 generates for the supplied query. The execution plan shows how
107 the table(s) referenced by the query will be scanned --- by plain
108 sequential scan, index scan etc --- and if multiple tables are
109 referenced, what join algorithms will be used to bring together
110 the required tuples from each input table.
114 The most critical part of the display is the estimated query execution
115 cost, which is the planner's guess at how long it will take to run the
116 query (measured in units of disk page fetches). Actually two numbers
117 are shown: the startup time before the first tuple can be returned, and
118 the total time to return all the tuples. For most queries the total time
119 is what matters, but in contexts such as an EXISTS sub-query the planner
120 will choose the smallest startup time instead of the smallest total time
121 (since the executor will stop after getting one tuple, anyway).
122 Also, if you limit the number of tuples to return with a LIMIT clause,
123 the planner makes an appropriate interpolation between the endpoint
124 costs to estimate which plan is really the cheapest.
128 The VERBOSE option emits the full internal representation of the plan tree,
129 rather than just a summary (and sends it to the postmaster log file, too).
130 Usually this option is only useful for debugging Postgres.
133 <refsect2 id="R2-SQL-EXPLAIN-3">
135 <date>1998-04-15</date>
141 There is only sparse documentation on the optimizer's use of cost
142 information in <productname>Postgres</productname>.
143 General information on cost estimation for query optimization
144 can be found in database textbooks.
145 Refer to the <citetitle>Programmer's Guide</citetitle>
146 in the chapters on indexes and the genetic query optimizer for
152 <refsect1 id="R1-SQL-EXPLAIN-2">
158 To show a query plan for a simple query on a table with a single
159 <type>int4</type> column and 128 rows:
162 EXPLAIN SELECT * FROM foo;
166 Seq Scan on foo (cost=0.00..2.28 rows=128 width=4)
174 For the same table with an index to support an
175 <firstterm>equijoin</firstterm> condition on the query,
176 <command>EXPLAIN</command> will show a different plan:
179 EXPLAIN SELECT * FROM foo WHERE i = 4;
183 Index Scan using fi on foo (cost=0.00..0.42 rows=1 width=4)
191 And finally, for the same table with an index to support an
192 <firstterm>equijoin</firstterm> condition on the query,
193 <command>EXPLAIN</command> will show the following for a query
194 using an aggregate function:
197 EXPLAIN SELECT sum(i) FROM foo WHERE i = 4;
201 Aggregate (cost=0.42..0.42 rows=1 width=4)
202 -> Index Scan using fi on foo (cost=0.00..0.42 rows=1 width=4)
208 Note that the specific numbers shown, and even the selected query
209 strategy, may vary between Postgres releases due to planner improvements.
213 <refsect1 id="R1-SQL-EXPLAIN-3">
218 <refsect2 id="R2-SQL-EXPLAIN-4">
220 <date>1998-09-01</date>
226 There is no <command>EXPLAIN</command> statement defined in SQL92.
232 <!-- Keep this comment at the end of the file
237 sgml-minimize-attributes:nil
238 sgml-always-quote-attributes:t
241 sgml-parent-document:nil
242 sgml-default-dtd-file:"../reference.ced"
243 sgml-exposed-tags:nil
244 sgml-local-catalogs:"/usr/lib/sgml/catalog"
245 sgml-local-ecat-files:nil