doc/src/sgml/ref/explain.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-EXPLAIN">
   7  <refmeta>
   8   <refentrytitle id="SQL-EXPLAIN-TITLE">EXPLAIN</refentrytitle>
   9   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  10  </refmeta>
  11
  12  <refnamediv>
  13   <refname>EXPLAIN</refname>
  14   <refpurpose>show the execution plan of a statement</refpurpose>
  15  </refnamediv>
  16
  17  <indexterm zone="sql-explain">
  18   <primary>EXPLAIN</primary>
  19  </indexterm>
  20
  21  <indexterm zone="sql-explain">
  22   <primary>prepared statements</primary>
  23   <secondary>showing the query plan</secondary>
  24  </indexterm>
  25
  26  <indexterm zone="sql-explain">
  27   <primary>cursor</primary>
  28   <secondary>showing the query plan</secondary>
  29  </indexterm>
  30
  31  <refsynopsisdiv>
  32 <synopsis>
  33 EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="parameter">statement</replaceable>
  34 </synopsis>
  35  </refsynopsisdiv>
  36
  37  <refsect1>
  38   <title>Description</title>
  39
  40   <para>
  41    This command displays the execution plan that the
  42    <productname>PostgreSQL</productname> planner generates for the
  43    supplied statement.  The execution plan shows how the table(s)
  44    referenced by the statement will be scanned &mdash; by plain sequential scan,
  45    index scan, etc. &mdash; and if multiple tables are referenced, what join
  46    algorithms will be used to bring together the required rows from
  47    each input table.
  48   </para>
  49
  50   <para>
  51    The most critical part of the display is the estimated statement execution
  52    cost, which is the planner's guess at how long it will take to run the
  53    statement (measured in units of disk page fetches).  Actually two numbers
  54    are shown: the start-up time before the first row can be returned, and
  55    the total time to return all the rows.  For most queries the total time
  56    is what matters, but in contexts such as a subquery in <literal>EXISTS</literal>, the planner
  57    will choose the smallest start-up time instead of the smallest total time
  58    (since the executor will stop after getting one row, anyway).
  59    Also, if you limit the number of rows to return with a <literal>LIMIT</literal> clause,
  60    the planner makes an appropriate interpolation between the endpoint
  61    costs to estimate which plan is really the cheapest.
  62   </para>
  63
  64   <para>
  65    The <literal>ANALYZE</literal> option causes the statement to be actually executed, not only
  66    planned.  The total elapsed time expended within each plan node (in
  67    milliseconds) and total number of rows it actually returned are added to
  68    the display.  This is useful for seeing whether the planner's estimates
  69    are close to reality.
  70   </para>
  71
  72   <important>
  73    <para>
  74     Keep in mind that the statement is actually executed when
  75     the <literal>ANALYZE</literal> option is used.  Although
  76     <command>EXPLAIN</command> will discard any output that a
  77     <command>SELECT</command> would return, other side effects of the
  78     statement will happen as usual.  If you wish to use
  79     <command>EXPLAIN ANALYZE</command> on an
  80     <command>INSERT</command>, <command>UPDATE</command>,
  81     <command>DELETE</command>, or <command>EXECUTE</command> statement
  82     without letting the command affect your data, use this approach:
  83 <programlisting>
  84 BEGIN;
  85 EXPLAIN ANALYZE ...;
  86 ROLLBACK;
  87 </programlisting>
  88    </para>
  89   </important>
  90  </refsect1>
  91
  92  <refsect1>
  93   <title>Parameters</title>
  94
  95   <variablelist>
  96    <varlistentry>
  97     <term><literal>ANALYZE</literal></term>
  98     <listitem>
  99      <para>
 100       Carry out the command and show the actual run times.
 101      </para>
 102     </listitem>
 103    </varlistentry>
 104
 105    <varlistentry>
 106     <term><literal>VERBOSE</literal></term>
 107     <listitem>
 108      <para>
 109       Include the output column list for each node in the plan tree.
 110      </para>
 111     </listitem>
 112    </varlistentry>
 113
 114    <varlistentry>
 115     <term><replaceable class="parameter">statement</replaceable></term>
 116     <listitem>
 117      <para>
 118       Any <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>,
 119       <command>DELETE</>, <command>VALUES</>, <command>EXECUTE</>, or
 120       <command>DECLARE</> statement, whose execution plan you wish to see.
 121      </para>
 122     </listitem>
 123    </varlistentry>
 124   </variablelist>
 125  </refsect1>
 126
 127  <refsect1>
 128   <title>Notes</title>
 129
 130   <para>
 131    There is only sparse documentation on the optimizer's use of cost
 132    information in <productname>PostgreSQL</productname>.  Refer to
 133    <xref linkend="using-explain"> for more information.
 134   </para>
 135
 136   <para>
 137    In order to allow the <productname>PostgreSQL</productname> query
 138    planner to make reasonably informed decisions when optimizing
 139    queries, the <xref linkend="sql-analyze" endterm="sql-analyze-title">
 140    statement should be run to record statistics about the distribution
 141    of data within the table. If you have not done this (or if the
 142    statistical distribution of the data in the table has changed
 143    significantly since the last time <command>ANALYZE</command> was
 144    run), the estimated costs are unlikely to conform to the real
 145    properties of the query, and consequently an inferior query plan
 146    might be chosen.
 147   </para>
 148
 149   <para>
 150    Genetic query optimization (<acronym>GEQO</acronym>) randomly tests
 151    execution plans.  Therefore, when the number of join relations
 152    exceeds <xref linkend="guc-geqo-threshold"> causing genetic query
 153    optimization to be used, the execution plan is likely to change
 154    each time the statement is executed.
 155   </para>
 156
 157   <para>
 158    In order to measure the run-time cost of each node in the execution
 159    plan, the current implementation of <command>EXPLAIN
 160    ANALYZE</command> can add considerable profiling overhead to query
 161    execution. As a result, running <command>EXPLAIN ANALYZE</command>
 162    on a query can sometimes take significantly longer than executing
 163    the query normally. The amount of overhead depends on the nature of
 164    the query.
 165   </para>
 166  </refsect1>
 167
 168  <refsect1>
 169   <title>Examples</title>
 170
 171   <para>
 172    To show the plan for a simple query on a table with a single
 173    <type>integer</type> column and 10000 rows:
 174
 175 <programlisting>
 176 EXPLAIN SELECT * FROM foo;
 177
 178                        QUERY PLAN
 179 ---------------------------------------------------------
 180  Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
 181 (1 row)
 182 </programlisting>
 183   </para>
 184
 185   <para>
 186    If there is an index and we use a query with an indexable
 187    <literal>WHERE</literal> condition, <command>EXPLAIN</command>
 188    might show a different plan:
 189
 190 <programlisting>
 191 EXPLAIN SELECT * FROM foo WHERE i = 4;
 192
 193                          QUERY PLAN
 194 --------------------------------------------------------------
 195  Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)
 196    Index Cond: (i = 4)
 197 (2 rows)
 198 </programlisting>
 199   </para>
 200
 201   <para>
 202    Here is an example of a query plan for a query using an aggregate
 203    function:
 204
 205 <programlisting>
 206 EXPLAIN SELECT sum(i) FROM foo WHERE i &lt; 10;
 207
 208                              QUERY PLAN
 209 ---------------------------------------------------------------------
 210  Aggregate  (cost=23.93..23.93 rows=1 width=4)
 211    -&gt;  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)
 212          Index Cond: (i &lt; 10)
 213 (3 rows)
 214 </programlisting>
 215   </para>
 216
 217   <para>
 218    Here is an example of using <command>EXPLAIN EXECUTE</command> to
 219    display the execution plan for a prepared query:
 220
 221 <programlisting>
 222 PREPARE query(int, int) AS SELECT sum(bar) FROM test
 223     WHERE id &gt; $1 AND id &lt; $2
 224     GROUP BY foo;
 225
 226 EXPLAIN ANALYZE EXECUTE query(100, 200);
 227
 228                                                        QUERY PLAN
 229 -------------------------------------------------------------------------------------------------------------------------
 230  HashAggregate  (cost=39.53..39.53 rows=1 width=8) (actual time=0.661..0.672 rows=7 loops=1)
 231    -&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)
 232          Index Cond: ((id &gt; $1) AND (id &lt; $2))
 233  Total runtime: 0.851 ms
 234 (4 rows)
 235 </programlisting>
 236   </para>
 237
 238   <para>
 239    Of course, the specific numbers shown here depend on the actual
 240    contents of the tables involved.  Also note that the numbers, and
 241    even the selected query strategy, might vary between
 242    <productname>PostgreSQL</productname> releases due to planner
 243    improvements. In addition, the <command>ANALYZE</command> command
 244    uses random sampling to estimate data statistics; therefore, it is
 245    possible for cost estimates to change after a fresh run of
 246    <command>ANALYZE</command>, even if the actual distribution of data
 247    in the table has not changed.
 248   </para>
 249  </refsect1>
 250
 251  <refsect1>
 252   <title>Compatibility</title>
 253
 254   <para>
 255    There is no <command>EXPLAIN</command> statement defined in the SQL standard.
 256   </para>
 257  </refsect1>
 258
 259  <refsect1>
 260   <title>See Also</title>
 261
 262   <simplelist type="inline">
 263    <member><xref linkend="sql-analyze" endterm="sql-analyze-title"></member>
 264   </simplelist>
 265  </refsect1>
 266 </refentry>