-
Thomas G. Lockhart authored
capabilities of specifying time zones as intervals per SQL9x. Put refentrytitle contents on the same line as the tag. Otherwise, leading whitespace is propagated into the product, which (at least) messes up the ToC layout. Remove (some) docinfo tags containing dates. Best to omit if the dates are not accurate; maybe use CVS dates instead or leave them out.
Thomas G. Lockhart authoredcapabilities of specifying time zones as intervals per SQL9x. Put refentrytitle contents on the same line as the tag. Otherwise, leading whitespace is propagated into the product, which (at least) messes up the ToC layout. Remove (some) docinfo tags containing dates. Best to omit if the dates are not accurate; maybe use CVS dates instead or leave them out.
explain.sgml 7.41 KiB
<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v 1.20 2002/04/21 19:02:39 thomas Exp $
PostgreSQL documentation
-->
<refentry id="SQL-EXPLAIN">
<refmeta>
<refentrytitle id="SQL-EXPLAIN-TITLE">EXPLAIN</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
<refname>
EXPLAIN
</refname>
<refpurpose>
show the execution plan of a statement
</refpurpose>
</refnamediv>
<refsynopsisdiv>
<refsynopsisdivinfo>
<date>1999-07-20</date>
</refsynopsisdivinfo>
<synopsis>
EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="PARAMETER">query</replaceable>
</synopsis>
<refsect2 id="R2-SQL-EXPLAIN-1">
<refsect2info>
<date>1998-09-01</date>
</refsect2info>
<title>
Inputs
</title>
<para>
<variablelist>
<varlistentry>
<term>ANALYZE</term>
<listitem>
<para>
Flag to carry out the query and show actual run times.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>VERBOSE</term>
<listitem>
<para>
Flag to show detailed query plan dump.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">query</replaceable></term>
<listitem>
<para>
Any <replaceable class="PARAMETER">query</replaceable>.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
</refsect2>
<refsect2 id="R2-SQL-EXPLAIN-2">
<refsect2info>
<date>1998-04-15</date>
</refsect2info>
<title> Outputs
</title>
<para>
<variablelist>
<varlistentry>
<term>Query plan</term>
<listitem>
<para>
Explicit query plan from the <productname>PostgreSQL</productname>
planner.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
<note>
<para>
Prior to <application>PostgreSQL</application> 7.3, the query plan
was emitted in the form of a NOTICE message. Now it appears as a
query result (formatted like a table with a single text column).
</para>
</note>
</refsect2>
</refsynopsisdiv>
<refsect1 id="R1-SQL-EXPLAIN-1">
<refsect1info>
<date>1998-04-15</date>
</refsect1info>
<title>
Description
</title>
<para>
This command displays the execution plan that the
<application>PostgreSQL</application> planner
generates for the supplied query. The execution plan shows how
the table(s) referenced by the query will be scanned---by plain
sequential scan, index scan, etc.---and if multiple tables are
referenced, what join algorithms will be used to bring together
the required tuples from each input table.
</para>
<para>
The most critical part of the display is the estimated query execution
cost, which is the planner's guess at how long it will take to run the
query (measured in units of disk page fetches). Actually two numbers
are shown: the start-up time before the first tuple can be returned, and
the total time to return all the tuples. For most queries the total time
is what matters, but in contexts such as an EXISTS sub-query the planner
will choose the smallest start-up time instead of the smallest total time
(since the executor will stop after getting one tuple, anyway).
Also, if you limit the number of tuples to return with a LIMIT clause,
the planner makes an appropriate interpolation between the endpoint
costs to estimate which plan is really the cheapest.
</para>
<para>
The ANALYZE option causes the query to be actually executed, not only
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
are close to reality.
</para>
<caution>
<para>
Keep in mind that the query is actually executed when ANALYZE is used. Although <command>EXPLAIN</command> will discard any output that a SELECT
would return,
other side-effects of the query will happen as usual.
If you wish to use <command>EXPLAIN ANALYZE</command> on an INSERT,
UPDATE, or DELETE query without letting the query affect your data,
use this approach:
<programlisting>
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;
</programlisting>
</para>
</caution>
<para>
The VERBOSE option emits the full internal representation of the plan tree,
rather than just a summary.
Usually this option is only useful for debugging
<application>PostgreSQL</application>. The VERBOSE dump is either
pretty-printed or not, depending on the setting of the
<option>EXPLAIN_PRETTY_PRINT</option> configuration parameter.
</para>
<refsect2 id="R2-SQL-EXPLAIN-3">
<refsect2info>
<date>1998-04-15</date>
</refsect2info>
<title>
Notes
</title>
<para>
There is only sparse documentation on the optimizer's use of cost
information in <productname>PostgreSQL</productname>.
Refer to the <citetitle>User's Guide</citetitle> and
<citetitle>Programmer's Guide</citetitle> for more information.
</para>
</refsect2>
</refsect1>
<refsect1 id="R1-SQL-EXPLAIN-2">
<title>
Usage
</title>
<para>
To show a query plan for a simple query on a table with a single
<type>int4</type> column and 10000 rows:
<programlisting>
EXPLAIN SELECT * FROM foo;
<computeroutput>
QUERY PLAN
---------------------------------------------------------
Seq Scan on foo (cost=0.00..155.00 rows=10000 width=4)
(1 row)
</computeroutput>
</programlisting>
</para>
<para>
If there is an index and we use a query with an indexable WHERE condition,
<command>EXPLAIN</command> will show a different plan:
<programlisting>
EXPLAIN SELECT * FROM foo WHERE i = 4;
<computeroutput>
QUERY PLAN
--------------------------------------------------------------
Index Scan using fi on foo (cost=0.00..5.98 rows=1 width=4)
Index Cond: (i = 4)(2 rows)
</computeroutput>
</programlisting>
</para>
<para>
And here is an example of a query plan for a query
using an aggregate function:
<programlisting>
EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;
<computeroutput>
QUERY PLAN
---------------------------------------------------------------------
Aggregate (cost=23.93..23.93 rows=1 width=4)
-> Index Scan using fi on foo (cost=0.00..23.92 rows=6 width=4)
Index Cond: (i < 10)
(3 rows)
</computeroutput>
</programlisting>
</para>
<para>
Note that the specific numbers shown, and even the selected query
strategy, may vary between <application>PostgreSQL</application>
releases due to planner improvements.
</para>
</refsect1>
<refsect1 id="R1-SQL-EXPLAIN-3">
<title>
Compatibility
</title>
<refsect2 id="R2-SQL-EXPLAIN-4">
<refsect2info>
<date>1998-09-01</date>
</refsect2info>
<title>
SQL92
</title>
<para>
There is no <command>EXPLAIN</command> statement defined in SQL92.
</para>
</refsect2>
</refsect1>
</refentry>
<!-- 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:
-->