doc/src/sgml/ref/explain.sgml

   1 <!--
   2 doc/src/sgml/ref/explain.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-explain">
   7  <indexterm zone="sql-explain">
   8   <primary>EXPLAIN</primary>
   9  </indexterm>
  10
  11  <indexterm zone="sql-explain">
  12   <primary>prepared statements</primary>
  13   <secondary>showing the query plan</secondary>
  14  </indexterm>
  15
  16  <indexterm zone="sql-explain">
  17   <primary>cursor</primary>
  18   <secondary>showing the query plan</secondary>
  19  </indexterm>
  20
  21  <refmeta>
  22   <refentrytitle>EXPLAIN</refentrytitle>
  23   <manvolnum>7</manvolnum>
  24   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  25  </refmeta>
  26
  27  <refnamediv>
  28   <refname>EXPLAIN</refname>
  29   <refpurpose>show the execution plan of a statement</refpurpose>
  30  </refnamediv>
  31
  32  <refsynopsisdiv>
  33 <synopsis>
  34 EXPLAIN [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] <replaceable class="parameter">statement</replaceable>
  35
  36 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
  37
  38     ANALYZE [ <replaceable class="parameter">boolean</replaceable> ]
  39     VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
  40     COSTS [ <replaceable class="parameter">boolean</replaceable> ]
  41     SETTINGS [ <replaceable class="parameter">boolean</replaceable> ]
  42     GENERIC_PLAN [ <replaceable class="parameter">boolean</replaceable> ]
  43     BUFFERS [ <replaceable class="parameter">boolean</replaceable> ]
  44     WAL [ <replaceable class="parameter">boolean</replaceable> ]
  45     TIMING [ <replaceable class="parameter">boolean</replaceable> ]
  46     SUMMARY [ <replaceable class="parameter">boolean</replaceable> ]
  47     FORMAT { TEXT | XML | JSON | YAML }
  48 </synopsis>
  49  </refsynopsisdiv>
  50
  51  <refsect1>
  52   <title>Description</title>
  53
  54   <para>
  55    This command displays the execution plan that the
  56    <productname>PostgreSQL</productname> planner generates for the
  57    supplied statement.  The execution plan shows how the table(s)
  58    referenced by the statement will be scanned &mdash; by plain sequential scan,
  59    index scan, etc. &mdash; and if multiple tables are referenced, what join
  60    algorithms will be used to bring together the required rows from
  61    each input table.
  62   </para>
  63
  64   <para>
  65    The most critical part of the display is the estimated statement execution
  66    cost, which is the planner's guess at how long it will take to run the
  67    statement (measured in cost units that are arbitrary, but conventionally
  68    mean disk page fetches).  Actually two numbers
  69    are shown: the start-up cost before the first row can be returned, and
  70    the total cost to return all the rows.  For most queries the total cost
  71    is what matters, but in contexts such as a subquery in <literal>EXISTS</literal>, the planner
  72    will choose the smallest start-up cost instead of the smallest total cost
  73    (since the executor will stop after getting one row, anyway).
  74    Also, if you limit the number of rows to return with a <literal>LIMIT</literal> clause,
  75    the planner makes an appropriate interpolation between the endpoint
  76    costs to estimate which plan is really the cheapest.
  77   </para>
  78
  79   <para>
  80    The <literal>ANALYZE</literal> option causes the statement to be actually
  81    executed, not only planned.  Then actual run time statistics are added to
  82    the display, including the total elapsed time expended within each plan
  83    node (in milliseconds) and the total number of rows it actually returned.
  84    This is useful for seeing whether the planner's estimates
  85    are close to reality.
  86   </para>
  87
  88   <important>
  89    <para>
  90     Keep in mind that the statement is actually executed when
  91     the <literal>ANALYZE</literal> option is used.  Although
  92     <command>EXPLAIN</command> will discard any output that a
  93     <command>SELECT</command> would return, other side effects of the
  94     statement will happen as usual.  If you wish to use
  95     <command>EXPLAIN ANALYZE</command> on an
  96     <command>INSERT</command>, <command>UPDATE</command>,
  97     <command>DELETE</command>, <command>MERGE</command>,
  98     <command>CREATE TABLE AS</command>,
  99     or <command>EXECUTE</command> statement
 100     without letting the command affect your data, use this approach:
 101 <programlisting>
 102 BEGIN;
 103 EXPLAIN ANALYZE ...;
 104 ROLLBACK;
 105 </programlisting>
 106    </para>
 107   </important>
 108  </refsect1>
 109
 110  <refsect1>
 111   <title>Parameters</title>
 112
 113   <variablelist>
 114    <varlistentry>
 115     <term><literal>ANALYZE</literal></term>
 116     <listitem>
 117      <para>
 118       Carry out the command and show actual run times and other statistics.
 119       This parameter defaults to <literal>FALSE</literal>.
 120      </para>
 121     </listitem>
 122    </varlistentry>
 123
 124    <varlistentry>
 125     <term><literal>VERBOSE</literal></term>
 126     <listitem>
 127      <para>
 128       Display additional information regarding the plan.  Specifically, include
 129       the output column list for each node in the plan tree, schema-qualify
 130       table and function names, always label variables in expressions with
 131       their range table alias, and always print the name of each trigger for
 132       which statistics are displayed.  The query identifier will also be
 133       displayed if one has been computed, see <xref
 134       linkend="guc-compute-query-id"/> for more details.  This parameter
 135       defaults to <literal>FALSE</literal>.
 136      </para>
 137     </listitem>
 138    </varlistentry>
 139
 140    <varlistentry>
 141     <term><literal>COSTS</literal></term>
 142     <listitem>
 143      <para>
 144       Include information on the estimated startup and total cost of each
 145       plan node, as well as the estimated number of rows and the estimated
 146       width of each row.
 147       This parameter defaults to <literal>TRUE</literal>.
 148      </para>
 149     </listitem>
 150    </varlistentry>
 151
 152    <varlistentry>
 153     <term><literal>SETTINGS</literal></term>
 154     <listitem>
 155      <para>
 156       Include information on configuration parameters.  Specifically, include
 157       options affecting query planning with value different from the built-in
 158       default value.  This parameter defaults to <literal>FALSE</literal>.
 159      </para>
 160     </listitem>
 161    </varlistentry>
 162
 163    <varlistentry>
 164     <term><literal>GENERIC_PLAN</literal></term>
 165     <listitem>
 166      <para>
 167       Allow the statement to contain parameter placeholders like
 168       <literal>$1</literal>, and generate a generic plan that does not
 169       depend on the values of those parameters.
 170       See <link linkend="sql-prepare"><command>PREPARE</command></link>
 171       for details about generic plans and the types of statement that
 172       support parameters.
 173       This parameter cannot be used together with <literal>ANALYZE</literal>.
 174       It defaults to <literal>FALSE</literal>.
 175      </para>
 176     </listitem>
 177    </varlistentry>
 178
 179    <varlistentry>
 180     <term><literal>BUFFERS</literal></term>
 181     <listitem>
 182      <para>
 183       Include information on buffer usage. Specifically, include the number of
 184       shared blocks hit, read, dirtied, and written, the number of local blocks
 185       hit, read, dirtied, and written, the number of temp blocks read and
 186       written, and the time spent reading and writing data file blocks, local
 187       blocks and temporary file blocks (in milliseconds) if
 188       <xref linkend="guc-track-io-timing"/> is enabled.  A
 189       <emphasis>hit</emphasis> means that a read was avoided because the block
 190       was found already in cache when needed.
 191       Shared blocks contain data from regular tables and indexes;
 192       local blocks contain data from temporary tables and indexes;
 193       while temporary blocks contain short-term working data used in sorts,
 194       hashes, Materialize plan nodes, and similar cases.
 195       The number of blocks <emphasis>dirtied</emphasis> indicates the number of
 196       previously unmodified blocks that were changed by this query; while the
 197       number of blocks <emphasis>written</emphasis> indicates the number of
 198       previously-dirtied blocks evicted from cache by this backend during
 199       query processing.
 200       The number of blocks shown for an
 201       upper-level node includes those used by all its child nodes.  In text
 202       format, only non-zero values are printed.  This parameter defaults to
 203       <literal>FALSE</literal>.
 204      </para>
 205     </listitem>
 206    </varlistentry>
 207
 208    <varlistentry>
 209     <term><literal>WAL</literal></term>
 210     <listitem>
 211      <para>
 212       Include information on WAL record generation. Specifically, include the
 213       number of records, number of full page images (fpi) and the amount of WAL
 214       generated in bytes. In text format, only non-zero values are printed.
 215       This parameter may only be used when <literal>ANALYZE</literal> is also
 216       enabled.  It defaults to <literal>FALSE</literal>.
 217      </para>
 218     </listitem>
 219    </varlistentry>
 220
 221    <varlistentry>
 222     <term><literal>TIMING</literal></term>
 223     <listitem>
 224      <para>
 225       Include actual startup time and time spent in each node in the output.
 226       The overhead of repeatedly reading the system clock can slow down the
 227       query significantly on some systems, so it may be useful to set this
 228       parameter to <literal>FALSE</literal> when only actual row counts, and
 229       not exact times, are needed.  Run time of the entire statement is
 230       always measured, even when node-level timing is turned off with this
 231       option.
 232       This parameter may only be used when <literal>ANALYZE</literal> is also
 233       enabled.  It defaults to <literal>TRUE</literal>.
 234      </para>
 235     </listitem>
 236    </varlistentry>
 237
 238    <varlistentry>
 239     <term><literal>SUMMARY</literal></term>
 240     <listitem>
 241      <para>
 242       Include summary information (e.g., totaled timing information) after the
 243       query plan.  Summary information is included by default when
 244       <literal>ANALYZE</literal> is used but otherwise is not included by
 245       default, but can be enabled using this option.  Planning time in
 246       <command>EXPLAIN EXECUTE</command> includes the time required to fetch
 247       the plan from the cache and the time required for re-planning, if
 248       necessary.
 249      </para>
 250     </listitem>
 251    </varlistentry>
 252
 253    <varlistentry>
 254     <term><literal>FORMAT</literal></term>
 255     <listitem>
 256      <para>
 257       Specify the output format, which can be TEXT, XML, JSON, or YAML.
 258       Non-text output contains the same information as the text output
 259       format, but is easier for programs to parse.  This parameter defaults to
 260       <literal>TEXT</literal>.
 261      </para>
 262     </listitem>
 263    </varlistentry>
 264
 265    <varlistentry>
 266     <term><replaceable class="parameter">boolean</replaceable></term>
 267     <listitem>
 268      <para>
 269       Specifies whether the selected option should be turned on or off.
 270       You can write <literal>TRUE</literal>, <literal>ON</literal>, or
 271       <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
 272       <literal>OFF</literal>, or <literal>0</literal> to disable it.  The
 273       <replaceable class="parameter">boolean</replaceable> value can also
 274       be omitted, in which case <literal>TRUE</literal> is assumed.
 275      </para>
 276     </listitem>
 277    </varlistentry>
 278
 279    <varlistentry>
 280     <term><replaceable class="parameter">statement</replaceable></term>
 281     <listitem>
 282      <para>
 283       Any <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>,
 284       <command>DELETE</command>, <command>MERGE</command>,
 285       <command>VALUES</command>, <command>EXECUTE</command>,
 286       <command>DECLARE</command>, <command>CREATE TABLE AS</command>, or
 287       <command>CREATE MATERIALIZED VIEW AS</command> statement, whose execution
 288       plan you wish to see.
 289      </para>
 290     </listitem>
 291    </varlistentry>
 292   </variablelist>
 293  </refsect1>
 294
 295  <refsect1>
 296   <title>Outputs</title>
 297
 298    <para>
 299     The command's result is a textual description of the plan selected
 300     for the <replaceable class="parameter">statement</replaceable>,
 301     optionally annotated with execution statistics.
 302     <xref linkend="using-explain"/> describes the information provided.
 303    </para>
 304  </refsect1>
 305
 306  <refsect1>
 307   <title>Notes</title>
 308
 309   <para>
 310    In order to allow the <productname>PostgreSQL</productname> query
 311    planner to make reasonably informed decisions when optimizing
 312    queries, the <link
 313    linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>
 314    data should be up-to-date for all tables used in the query.  Normally
 315    the <link linkend="autovacuum">autovacuum daemon</link> will take care
 316    of that automatically.  But if a table has recently had substantial
 317    changes in its contents, you might need to do a manual
 318    <link linkend="sql-analyze"><command>ANALYZE</command></link> rather than wait for autovacuum to catch up
 319    with the changes.
 320   </para>
 321
 322   <para>
 323    In order to measure the run-time cost of each node in the execution
 324    plan, the current implementation of <command>EXPLAIN
 325    ANALYZE</command> adds profiling overhead to query execution.
 326    As a result, running <command>EXPLAIN ANALYZE</command>
 327    on a query can sometimes take significantly longer than executing
 328    the query normally. The amount of overhead depends on the nature of
 329    the query, as well as the platform being used.  The worst case occurs
 330    for plan nodes that in themselves require very little time per
 331    execution, and on machines that have relatively slow operating
 332    system calls for obtaining the time of day.
 333   </para>
 334  </refsect1>
 335
 336  <refsect1>
 337   <title>Examples</title>
 338
 339   <para>
 340    To show the plan for a simple query on a table with a single
 341    <type>integer</type> column and 10000 rows:
 342
 343 <programlisting>
 344 EXPLAIN SELECT * FROM foo;
 345
 346                        QUERY PLAN
 347 ---------------------------------------------------------
 348  Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
 349 (1 row)
 350 </programlisting>
 351   </para>
 352
 353   <para>
 354   Here is the same query, with JSON output formatting:
 355 <programlisting>
 356 EXPLAIN (FORMAT JSON) SELECT * FROM foo;
 357            QUERY PLAN
 358 --------------------------------
 359  [                             +
 360    {                           +
 361      "Plan": {                 +
 362        "Node Type": "Seq Scan",+
 363        "Relation Name": "foo", +
 364        "Alias": "foo",         +
 365        "Startup Cost": 0.00,   +
 366        "Total Cost": 155.00,   +
 367        "Plan Rows": 10000,     +
 368        "Plan Width": 4         +
 369      }                         +
 370    }                           +
 371  ]
 372 (1 row)
 373 </programlisting>
 374   </para>
 375
 376   <para>
 377    If there is an index and we use a query with an indexable
 378    <literal>WHERE</literal> condition, <command>EXPLAIN</command>
 379    might show a different plan:
 380
 381 <programlisting>
 382 EXPLAIN SELECT * FROM foo WHERE i = 4;
 383
 384                          QUERY PLAN
 385 --------------------------------------------------------------
 386  Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)
 387    Index Cond: (i = 4)
 388 (2 rows)
 389 </programlisting>
 390   </para>
 391
 392   <para>
 393   Here is the same query, but in YAML format:
 394 <programlisting>
 395 EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';
 396           QUERY PLAN
 397 -------------------------------
 398  - Plan:                      +
 399      Node Type: "Index Scan"  +
 400      Scan Direction: "Forward"+
 401      Index Name: "fi"         +
 402      Relation Name: "foo"     +
 403      Alias: "foo"             +
 404      Startup Cost: 0.00       +
 405      Total Cost: 5.98         +
 406      Plan Rows: 1             +
 407      Plan Width: 4            +
 408      Index Cond: "(i = 4)"
 409 (1 row)
 410 </programlisting>
 411
 412     XML format is left as an exercise for the reader.
 413   </para>
 414   <para>
 415    Here is the same plan with cost estimates suppressed:
 416
 417 <programlisting>
 418 EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;
 419
 420         QUERY PLAN
 421 ----------------------------
 422  Index Scan using fi on foo
 423    Index Cond: (i = 4)
 424 (2 rows)
 425 </programlisting>
 426   </para>
 427
 428   <para>
 429    Here is an example of a query plan for a query using an aggregate
 430    function:
 431
 432 <programlisting>
 433 EXPLAIN SELECT sum(i) FROM foo WHERE i &lt; 10;
 434
 435                              QUERY PLAN
 436 -------------------------------------------------------------------&zwsp;--
 437  Aggregate  (cost=23.93..23.93 rows=1 width=4)
 438    -&gt;  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)
 439          Index Cond: (i &lt; 10)
 440 (3 rows)
 441 </programlisting>
 442   </para>
 443
 444   <para>
 445    Here is an example of using <command>EXPLAIN EXECUTE</command> to
 446    display the execution plan for a prepared query:
 447
 448 <programlisting>
 449 PREPARE query(int, int) AS SELECT sum(bar) FROM test
 450     WHERE id &gt; $1 AND id &lt; $2
 451     GROUP BY foo;
 452
 453 EXPLAIN ANALYZE EXECUTE query(100, 200);
 454
 455                                                        QUERY PLAN
 456 -------------------------------------------------------------------&zwsp;------------------------------------------------------
 457  HashAggregate  (cost=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10 loops=1)
 458    Group Key: foo
 459    Batches: 1  Memory Usage: 24kB
 460    -&gt;  Index Scan using test_pkey on test  (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99 loops=1)
 461          Index Cond: ((id &gt; 100) AND (id &lt; 200))
 462  Planning Time: 0.244 ms
 463  Execution Time: 0.073 ms
 464 (7 rows)
 465 </programlisting>
 466   </para>
 467
 468   <para>
 469    Of course, the specific numbers shown here depend on the actual
 470    contents of the tables involved.  Also note that the numbers, and
 471    even the selected query strategy, might vary between
 472    <productname>PostgreSQL</productname> releases due to planner
 473    improvements. In addition, the <command>ANALYZE</command> command
 474    uses random sampling to estimate data statistics; therefore, it is
 475    possible for cost estimates to change after a fresh run of
 476    <command>ANALYZE</command>, even if the actual distribution of data
 477    in the table has not changed.
 478   </para>
 479
 480   <para>
 481    Notice that the previous example showed a <quote>custom</quote> plan
 482    for the specific parameter values given in <command>EXECUTE</command>.
 483    We might also wish to see the generic plan for a parameterized
 484    query, which can be done with <literal>GENERIC_PLAN</literal>:
 485
 486 <programlisting>
 487 EXPLAIN (GENERIC_PLAN)
 488   SELECT sum(bar) FROM test
 489     WHERE id &gt; $1 AND id &lt; $2
 490     GROUP BY foo;
 491
 492                                   QUERY PLAN
 493 -------------------------------------------------------------------&zwsp;------------
 494  HashAggregate  (cost=26.79..26.89 rows=10 width=12)
 495    Group Key: foo
 496    -&gt;  Index Scan using test_pkey on test  (cost=0.29..24.29 rows=500 width=8)
 497          Index Cond: ((id &gt; $1) AND (id &lt; $2))
 498 (4 rows)
 499 </programlisting>
 500
 501    In this case the parser correctly inferred that <literal>$1</literal>
 502    and <literal>$2</literal> should have the same data type
 503    as <literal>id</literal>, so the lack of parameter type information
 504    from <command>PREPARE</command> was not a problem.  In other cases
 505    it might be necessary to explicitly specify types for the parameter
 506    symbols, which can be done by casting them, for example:
 507
 508 <programlisting>
 509 EXPLAIN (GENERIC_PLAN)
 510   SELECT sum(bar) FROM test
 511     WHERE id &gt; $1::integer AND id &lt; $2::integer
 512     GROUP BY foo;
 513 </programlisting>
 514   </para>
 515  </refsect1>
 516
 517  <refsect1>
 518   <title>Compatibility</title>
 519
 520   <para>
 521    There is no <command>EXPLAIN</command> statement defined in the SQL standard.
 522   </para>
 523
 524   <para>
 525    The following syntax was used before <productname>PostgreSQL</productname>
 526    version 9.0 and is still supported:
 527 <synopsis>
 528 EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="parameter">statement</replaceable>
 529 </synopsis>
 530    Note that in this syntax, the options must be specified in exactly the order
 531    shown.
 532   </para>
 533  </refsect1>
 534
 535  <refsect1>
 536   <title>See Also</title>
 537
 538   <simplelist type="inline">
 539    <member><xref linkend="sql-analyze"/></member>
 540   </simplelist>
 541  </refsect1>
 542 </refentry>