<!--
doc/src/sgml/ref/explain.sgml
PostgreSQL documentation
-->

<refentry id="sql-explain">
 <indexterm zone="sql-explain">
  <primary>EXPLAIN</primary>
 </indexterm>

 <indexterm zone="sql-explain">
  <primary>prepared statements</primary>
  <secondary>showing the query plan</secondary>
 </indexterm>

 <indexterm zone="sql-explain">
  <primary>cursor</primary>
  <secondary>showing the query plan</secondary>
 </indexterm>

 <refmeta>
  <refentrytitle>EXPLAIN</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>EXPLAIN</refname>
  <refpurpose>show the execution plan of a statement</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
EXPLAIN [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] <replaceable class="parameter">statement</replaceable>

<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>

    ANALYZE [ <replaceable class="parameter">boolean</replaceable> ]
    VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
    COSTS [ <replaceable class="parameter">boolean</replaceable> ]
    SETTINGS [ <replaceable class="parameter">boolean</replaceable> ]
    GENERIC_PLAN [ <replaceable class="parameter">boolean</replaceable> ]
    BUFFERS [ <replaceable class="parameter">boolean</replaceable> ]
    SERIALIZE [ { NONE | TEXT | BINARY } ]
    WAL [ <replaceable class="parameter">boolean</replaceable> ]
    TIMING [ <replaceable class="parameter">boolean</replaceable> ]
    SUMMARY [ <replaceable class="parameter">boolean</replaceable> ]
    MEMORY [ <replaceable class="parameter">boolean</replaceable> ]
    FORMAT { TEXT | XML | JSON | YAML }
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   This command displays the execution plan that the
   <productname>PostgreSQL</productname> planner generates for the
   supplied statement.  The execution plan shows how the table(s)
   referenced by the statement will be scanned &mdash; by plain sequential scan,
   index scan, etc. &mdash; and if multiple tables are referenced, what join
   algorithms will be used to bring together the required rows from
   each input table.
  </para>

  <para>
   The most critical part of the display is the estimated statement execution
   cost, which is the planner's guess at how long it will take to run the
   statement (measured in cost units that are arbitrary, but conventionally
   mean disk page fetches).  Actually two numbers
   are shown: the start-up cost before the first row can be returned, and
   the total cost to return all the rows.  For most queries the total cost
   is what matters, but in contexts such as a subquery in <literal>EXISTS</literal>, the planner
   will choose the smallest start-up cost instead of the smallest total cost
   (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,
   the planner makes an appropriate interpolation between the endpoint
   costs to estimate which plan is really the cheapest.
  </para>

  <para>
   The <literal>ANALYZE</literal> option causes the statement to be actually
   executed, not only planned.  Then actual run time statistics are added to
   the display, including the total elapsed time expended within each plan
   node (in milliseconds) and the total number of rows it actually returned.
   This is useful for seeing whether the planner's estimates
   are close to reality.
  </para>

  <important>
   <para>
    Keep in mind that the statement is actually executed when
    the <literal>ANALYZE</literal> option 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>,