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