explain.sgml 8.42 KB
Newer Older
1
<!--
2
$Header: /cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v 1.31 2003/10/17 01:14:26 tgl Exp $
3
PostgreSQL documentation
4 5
-->

6 7
<refentry id="SQL-EXPLAIN">
 <refmeta>
8
  <refentrytitle id="SQL-EXPLAIN-TITLE">EXPLAIN</refentrytitle>
9 10
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
11

12
 <refnamediv>
13 14
  <refname>EXPLAIN</refname>
  <refpurpose>show the execution plan of a statement</refpurpose>
15
 </refnamediv>
16

Peter Eisentraut's avatar
Peter Eisentraut committed
17 18 19 20
 <indexterm zone="sql-explain">
  <primary>EXPLAIN</primary>
 </indexterm>

21
 <refsynopsisdiv>
22 23 24
<synopsis>
EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="parameter">statement</replaceable>
</synopsis>
25
 </refsynopsisdiv>
26

27 28
 <refsect1>
  <title>Description</title>
29

30
  <para>
31
   This command displays the execution plan that the
Bruce Momjian's avatar
Bruce Momjian committed
32
   <productname>PostgreSQL</productname> planner generates for the
Tom Lane's avatar
Tom Lane committed
33
   supplied statement.  The execution plan shows how the table(s)
34
   referenced by the statement will be scanned---by plain sequential scan,
Bruce Momjian's avatar
Bruce Momjian committed
35
   index scan, etc.---and if multiple tables are referenced, what join
36
   algorithms will be used to bring together the required row from
Bruce Momjian's avatar
Bruce Momjian committed
37
   each input table.
38 39 40
  </para>

  <para>
41
   The most critical part of the display is the estimated statement execution
42
   cost, which is the planner's guess at how long it will take to run the
43 44 45 46
   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 <literal>EXISTS</literal>, the planner
47
   will choose the smallest start-up time instead of the smallest total time
48 49
   (since the executor will stop after getting one row, anyway).
   Also, if you limit the number of rows to return with a <literal>LIMIT</literal> clause,
50 51 52 53
   the planner makes an appropriate interpolation between the endpoint
   costs to estimate which plan is really the cheapest.
  </para>

54
  <para>
55
   The <literal>ANALYZE</literal> option causes the statement to be actually executed, not only
56 57 58
   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
59
   are close to reality.
60 61
  </para>

62
  <important>
63
   <para>
64 65 66 67 68 69 70 71 72 73
    Keep in mind that the statement is actually executed when
    <literal>ANALYZE</literal> is used.  Although
    <command>EXPLAIN</command> will discard any output that a
    <command>SELECT</command> would return, other side effects of the
    statement will happen as usual.  If you wish to use
    <command>EXPLAIN ANALYZE</command> on an
    <command>INSERT</command>, <command>UPDATE</command>,
    <command>DELETE</command>, or <command>EXECUTE</command> statement
    without letting the command affect your data, use this approach:
<programlisting>
74 75 76
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;
77
</programlisting>
78
   </para>
79 80 81 82
  </important>
 </refsect1>

 <refsect1>
83
  <title>Parameters</title>
84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148

  <variablelist>
   <varlistentry>
    <term><literal>ANALYZE</literal></term>
    <listitem>
     <para>
      Carry out the command and show the actual run times.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>VERBOSE</literal></term>
    <listitem>
     <para>
      Show the full internal representation of the plan tree, rather
      than just a summary.  Usually this option is only useful for
      debugging <productname>PostgreSQL</productname>.  The
      <literal>VERBOSE</literal> output is either pretty-printed or
      not, depending on the setting of the
      <varname>explain_pretty_print</varname> configuration parameter.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">statement</replaceable></term>
    <listitem>
     <para>
      Any <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>,
      <command>DELETE</>, <command>EXECUTE</>, or <command>DECLARE</>
      statement, whose execution plan you wish to see.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Notes</title>

  <para>
   There is only sparse documentation on the optimizer's use of cost
   information in <productname>PostgreSQL</productname>.  Refer to
   <xref linkend="using-explain"> for more information.
  </para>

  <para>
   In order to allow the <productname>PostgreSQL</productname> query
   planner to make reasonably informed decisions when optimizing
   queries, the <command>ANALYZE</command> 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 <command>ANALYZE</command> was run), the
   estimated costs are unlikely to conform to the real properties of
   the query, and consequently an inferior query plan may be chosen.
  </para>

  <para>
   Prior to <productname>PostgreSQL</productname> 7.3, the plan was
   emitted in the form of a <literal>NOTICE</literal> message.  Now it
   appears as a query result (formatted like a table with a single
   text column).
  </para>
149
 </refsect1>
150

151 152
 <refsect1>
  <title>Examples</title>
153 154

  <para>
155 156
   To show the plan for a simple query on a table with a single
   <type>integer</type> column and 10000 rows:
157

158
<programlisting>
159
EXPLAIN SELECT * FROM foo;
160

161 162 163 164
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
(1 row)
165
</programlisting>
166 167
  </para>

168
  <para>
169 170 171
   If there is an index and we use a query with an indexable
   <literal>WHERE</literal> condition, <command>EXPLAIN</command>
   might show a different plan:
172

173
<programlisting>
174
EXPLAIN SELECT * FROM foo WHERE i = 4;
175

176 177 178
                         QUERY PLAN
--------------------------------------------------------------
 Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)
179
   Index Cond: (i = 4)
180
(2 rows)
181
</programlisting>
182 183 184
  </para>

  <para>
185
   And here is an example of a query plan for a query
186 187
   using an aggregate function:

188
<programlisting>
Tom Lane's avatar
Tom Lane committed
189
EXPLAIN SELECT sum(i) FROM foo WHERE i &lt; 10;
190

191 192 193
                             QUERY PLAN
---------------------------------------------------------------------
 Aggregate  (cost=23.93..23.93 rows=1 width=4)
Tom Lane's avatar
Tom Lane committed
194
   -&gt;  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)
195
         Index Cond: (i &lt; 10)
196
(3 rows)
197
</programlisting>
198
  </para>
199

200
  <para>
201 202
   Here is an example of using <command>EXPLAIN EXECUTE</command> to
   display the execution plan for a prepared query:
203

204
<programlisting>
205 206 207 208 209
PREPARE query(int, int) AS SELECT sum(bar) FROM test
    WHERE id &gt; $1 AND id &lt; $2
    GROUP BY foo;

EXPLAIN ANALYZE EXECUTE query(100, 200);
210

211 212
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
213 214
 HashAggregate  (cost=39.53..39.53 rows=1 width=8) (actual time=0.661..0.672 rows=7 loops=1)
   -&gt;  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)
215
         Index Cond: ((id &gt; $1) AND (id &lt; $2))
216
 Total runtime: 0.851 ms
217
(4 rows)
218
</programlisting>
219 220
  </para>

221
  <para>
222 223 224 225 226 227 228
   Of course, the specific numbers shown here depend on the actual
   contents of the tables involved.  Also note that the numbers, and
   even the selected query strategy, may vary between
   <productname>PostgreSQL</productname> releases due to planner
   improvements. In addition, the <command>ANALYZE</command> command
   uses random sampling to estimate data statistics; therefore, it is
   possible for cost estimates to change after a fresh run of
229 230
   <command>ANALYZE</command>, even if the actual distribution of data
   in the table has not changed.
231
  </para>
232
 </refsect1>
233

234 235 236 237 238 239
 <refsect1>
  <title>Compatibility</title>

  <para>
   There is no <command>EXPLAIN</command> statement defined in the SQL standard.
  </para>
240 241
 </refsect1>
</refentry>
242

243 244 245 246 247 248 249 250 251 252 253 254 255 256 257
<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
258
-->