summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/sql-explain.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
commit5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/sql-explain.html
parentInitial commit. (diff)
downloadpostgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/sql-explain.html')
-rw-r--r--doc/src/sgml/html/sql-explain.html306
1 files changed, 306 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-explain.html b/doc/src/sgml/html/sql-explain.html
new file mode 100644
index 0000000..fb29950
--- /dev/null
+++ b/doc/src/sgml/html/sql-explain.html
@@ -0,0 +1,306 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>EXPLAIN</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-execute.html" title="EXECUTE" /><link rel="next" href="sql-fetch.html" title="FETCH" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">EXPLAIN</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-execute.html" title="EXECUTE">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-fetch.html" title="FETCH">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-EXPLAIN"><div class="titlepage"></div><a id="id-1.9.3.148.1" class="indexterm"></a><a id="id-1.9.3.148.2" class="indexterm"></a><a id="id-1.9.3.148.3" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">EXPLAIN</span></h2><p>EXPLAIN — show the execution plan of a statement</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
+EXPLAIN [ ( <em class="replaceable"><code>option</code></em> [, ...] ) ] <em class="replaceable"><code>statement</code></em>
+EXPLAIN [ ANALYZE ] [ VERBOSE ] <em class="replaceable"><code>statement</code></em>
+
+<span class="phrase">where <em class="replaceable"><code>option</code></em> can be one of:</span>
+
+ ANALYZE [ <em class="replaceable"><code>boolean</code></em> ]
+ VERBOSE [ <em class="replaceable"><code>boolean</code></em> ]
+ COSTS [ <em class="replaceable"><code>boolean</code></em> ]
+ SETTINGS [ <em class="replaceable"><code>boolean</code></em> ]
+ BUFFERS [ <em class="replaceable"><code>boolean</code></em> ]
+ WAL [ <em class="replaceable"><code>boolean</code></em> ]
+ TIMING [ <em class="replaceable"><code>boolean</code></em> ]
+ SUMMARY [ <em class="replaceable"><code>boolean</code></em> ]
+ FORMAT { TEXT | XML | JSON | YAML }
+</pre></div><div class="refsect1" id="id-1.9.3.148.7"><h2>Description</h2><p>
+ This command displays the execution plan that the
+ <span class="productname">PostgreSQL</span> planner generates for the
+ supplied statement. The execution plan shows how the table(s)
+ referenced by the statement 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 rows from
+ each input table.
+ </p><p>
+ 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 <code class="literal">EXISTS</code>, 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 <code class="literal">LIMIT</code> clause,
+ the planner makes an appropriate interpolation between the endpoint
+ costs to estimate which plan is really the cheapest.
+ </p><p>
+ The <code class="literal">ANALYZE</code> 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.
+ </p><div class="important"><h3 class="title">Important</h3><p>
+ Keep in mind that the statement is actually executed when
+ the <code class="literal">ANALYZE</code> option is used. Although
+ <code class="command">EXPLAIN</code> will discard any output that a
+ <code class="command">SELECT</code> would return, other side effects of the
+ statement will happen as usual. If you wish to use
+ <code class="command">EXPLAIN ANALYZE</code> on an
+ <code class="command">INSERT</code>, <code class="command">UPDATE</code>,
+ <code class="command">DELETE</code>, <code class="command">MERGE</code>,
+ <code class="command">CREATE TABLE AS</code>,
+ or <code class="command">EXECUTE</code> statement
+ without letting the command affect your data, use this approach:
+</p><pre class="programlisting">
+BEGIN;
+EXPLAIN ANALYZE ...;
+ROLLBACK;
+</pre><p>
+ </p></div><p>
+ Only the <code class="literal">ANALYZE</code> and <code class="literal">VERBOSE</code> options
+ can be specified, and only in that order, without surrounding the option
+ list in parentheses. Prior to <span class="productname">PostgreSQL</span> 9.0,
+ the unparenthesized syntax was the only one supported. It is expected that
+ all new options will be supported only in the parenthesized syntax.
+ </p></div><div class="refsect1" id="id-1.9.3.148.8"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">ANALYZE</code></span></dt><dd><p>
+ Carry out the command and show actual run times and other statistics.
+ This parameter defaults to <code class="literal">FALSE</code>.
+ </p></dd><dt><span class="term"><code class="literal">VERBOSE</code></span></dt><dd><p>
+ Display additional information regarding the plan. Specifically, include
+ the output column list for each node in the plan tree, schema-qualify
+ table and function names, always label variables in expressions with
+ their range table alias, and always print the name of each trigger for
+ which statistics are displayed. The query identifier will also be
+ displayed if one has been computed, see <a class="xref" href="runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID">compute_query_id</a> for more details. This parameter
+ defaults to <code class="literal">FALSE</code>.
+ </p></dd><dt><span class="term"><code class="literal">COSTS</code></span></dt><dd><p>
+ Include information on the estimated startup and total cost of each
+ plan node, as well as the estimated number of rows and the estimated
+ width of each row.
+ This parameter defaults to <code class="literal">TRUE</code>.
+ </p></dd><dt><span class="term"><code class="literal">SETTINGS</code></span></dt><dd><p>
+ Include information on configuration parameters. Specifically, include
+ options affecting query planning with value different from the built-in
+ default value. This parameter defaults to <code class="literal">FALSE</code>.
+ </p></dd><dt><span class="term"><code class="literal">BUFFERS</code></span></dt><dd><p>
+ Include information on buffer usage. Specifically, include the number of
+ shared blocks hit, read, dirtied, and written, the number of local blocks
+ hit, read, dirtied, and written, the number of temp blocks read and
+ written, and the time spent reading and writing data file blocks and
+ temporary file blocks (in milliseconds) if
+ <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> is enabled. A
+ <span class="emphasis"><em>hit</em></span> means that a read was avoided because the block
+ was found already in cache when needed.
+ Shared blocks contain data from regular tables and indexes;
+ local blocks contain data from temporary tables and indexes;
+ while temporary blocks contain short-term working data used in sorts,
+ hashes, Materialize plan nodes, and similar cases.
+ The number of blocks <span class="emphasis"><em>dirtied</em></span> indicates the number of
+ previously unmodified blocks that were changed by this query; while the
+ number of blocks <span class="emphasis"><em>written</em></span> indicates the number of
+ previously-dirtied blocks evicted from cache by this backend during
+ query processing.
+ The number of blocks shown for an
+ upper-level node includes those used by all its child nodes. In text
+ format, only non-zero values are printed. It defaults to
+ <code class="literal">FALSE</code>.
+ </p></dd><dt><span class="term"><code class="literal">WAL</code></span></dt><dd><p>
+ Include information on WAL record generation. Specifically, include the
+ number of records, number of full page images (fpi) and the amount of WAL
+ generated in bytes. In text format, only non-zero values are printed.
+ This parameter may only be used when <code class="literal">ANALYZE</code> is also
+ enabled. It defaults to <code class="literal">FALSE</code>.
+ </p></dd><dt><span class="term"><code class="literal">TIMING</code></span></dt><dd><p>
+ Include actual startup time and time spent in each node in the output.
+ The overhead of repeatedly reading the system clock can slow down the
+ query significantly on some systems, so it may be useful to set this
+ parameter to <code class="literal">FALSE</code> when only actual row counts, and
+ not exact times, are needed. Run time of the entire statement is
+ always measured, even when node-level timing is turned off with this
+ option.
+ This parameter may only be used when <code class="literal">ANALYZE</code> is also
+ enabled. It defaults to <code class="literal">TRUE</code>.
+ </p></dd><dt><span class="term"><code class="literal">SUMMARY</code></span></dt><dd><p>
+ Include summary information (e.g., totaled timing information) after the
+ query plan. Summary information is included by default when
+ <code class="literal">ANALYZE</code> is used but otherwise is not included by
+ default, but can be enabled using this option. Planning time in
+ <code class="command">EXPLAIN EXECUTE</code> includes the time required to fetch
+ the plan from the cache and the time required for re-planning, if
+ necessary.
+ </p></dd><dt><span class="term"><code class="literal">FORMAT</code></span></dt><dd><p>
+ Specify the output format, which can be TEXT, XML, JSON, or YAML.
+ Non-text output contains the same information as the text output
+ format, but is easier for programs to parse. This parameter defaults to
+ <code class="literal">TEXT</code>.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>boolean</code></em></span></dt><dd><p>
+ Specifies whether the selected option should be turned on or off.
+ You can write <code class="literal">TRUE</code>, <code class="literal">ON</code>, or
+ <code class="literal">1</code> to enable the option, and <code class="literal">FALSE</code>,
+ <code class="literal">OFF</code>, or <code class="literal">0</code> to disable it. The
+ <em class="replaceable"><code>boolean</code></em> value can also
+ be omitted, in which case <code class="literal">TRUE</code> is assumed.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>statement</code></em></span></dt><dd><p>
+ Any <code class="command">SELECT</code>, <code class="command">INSERT</code>, <code class="command">UPDATE</code>,
+ <code class="command">DELETE</code>, <code class="command">MERGE</code>,
+ <code class="command">VALUES</code>, <code class="command">EXECUTE</code>,
+ <code class="command">DECLARE</code>, <code class="command">CREATE TABLE AS</code>, or
+ <code class="command">CREATE MATERIALIZED VIEW AS</code> statement, whose execution
+ plan you wish to see.
+ </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.148.9"><h2>Outputs</h2><p>
+ The command's result is a textual description of the plan selected
+ for the <em class="replaceable"><code>statement</code></em>,
+ optionally annotated with execution statistics.
+ <a class="xref" href="using-explain.html" title="14.1. Using EXPLAIN">Section 14.1</a> describes the information provided.
+ </p></div><div class="refsect1" id="id-1.9.3.148.10"><h2>Notes</h2><p>
+ In order to allow the <span class="productname">PostgreSQL</span> query
+ planner to make reasonably informed decisions when optimizing
+ queries, the <a class="link" href="catalog-pg-statistic.html" title="53.51. pg_statistic"><code class="structname">pg_statistic</code></a>
+ data should be up-to-date for all tables used in the query. Normally
+ the <a class="link" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">autovacuum daemon</a> will take care
+ of that automatically. But if a table has recently had substantial
+ changes in its contents, you might need to do a manual
+ <a class="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></a> rather than wait for autovacuum to catch up
+ with the changes.
+ </p><p>
+ In order to measure the run-time cost of each node in the execution
+ plan, the current implementation of <code class="command">EXPLAIN
+ ANALYZE</code> adds profiling overhead to query execution.
+ As a result, running <code class="command">EXPLAIN ANALYZE</code>
+ on a query can sometimes take significantly longer than executing
+ the query normally. The amount of overhead depends on the nature of
+ the query, as well as the platform being used. The worst case occurs
+ for plan nodes that in themselves require very little time per
+ execution, and on machines that have relatively slow operating
+ system calls for obtaining the time of day.
+ </p></div><div class="refsect1" id="id-1.9.3.148.11"><h2>Examples</h2><p>
+ To show the plan for a simple query on a table with a single
+ <code class="type">integer</code> column and 10000 rows:
+
+</p><pre class="programlisting">
+EXPLAIN SELECT * FROM foo;
+
+ QUERY PLAN
+---------------------------------------------------------
+ Seq Scan on foo (cost=0.00..155.00 rows=10000 width=4)
+(1 row)
+</pre><p>
+ </p><p>
+ Here is the same query, with JSON output formatting:
+</p><pre class="programlisting">
+EXPLAIN (FORMAT JSON) SELECT * FROM foo;
+ QUERY PLAN
+--------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Seq Scan",+
+ "Relation Name": "foo", +
+ "Alias": "foo", +
+ "Startup Cost": 0.00, +
+ "Total Cost": 155.00, +
+ "Plan Rows": 10000, +
+ "Plan Width": 4 +
+ } +
+ } +
+ ]
+(1 row)
+</pre><p>
+ </p><p>
+ If there is an index and we use a query with an indexable
+ <code class="literal">WHERE</code> condition, <code class="command">EXPLAIN</code>
+ might show a different plan:
+
+</p><pre class="programlisting">
+EXPLAIN SELECT * FROM foo WHERE i = 4;
+
+ QUERY PLAN
+--------------------------------------------------------------
+ Index Scan using fi on foo (cost=0.00..5.98 rows=1 width=4)
+ Index Cond: (i = 4)
+(2 rows)
+</pre><p>
+ </p><p>
+ Here is the same query, but in YAML format:
+</p><pre class="programlisting">
+EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';
+ QUERY PLAN
+-------------------------------
+ - Plan: +
+ Node Type: "Index Scan" +
+ Scan Direction: "Forward"+
+ Index Name: "fi" +
+ Relation Name: "foo" +
+ Alias: "foo" +
+ Startup Cost: 0.00 +
+ Total Cost: 5.98 +
+ Plan Rows: 1 +
+ Plan Width: 4 +
+ Index Cond: "(i = 4)"
+(1 row)
+</pre><p>
+
+ XML format is left as an exercise for the reader.
+ </p><p>
+ Here is the same plan with cost estimates suppressed:
+
+</p><pre class="programlisting">
+EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;
+
+ QUERY PLAN
+----------------------------
+ Index Scan using fi on foo
+ Index Cond: (i = 4)
+(2 rows)
+</pre><p>
+ </p><p>
+ Here is an example of a query plan for a query using an aggregate
+ function:
+
+</p><pre class="programlisting">
+EXPLAIN SELECT sum(i) FROM foo WHERE i &lt; 10;
+
+ QUERY PLAN
+-------------------------------------------------------------------​--
+ Aggregate (cost=23.93..23.93 rows=1 width=4)
+ -&gt; Index Scan using fi on foo (cost=0.00..23.92 rows=6 width=4)
+ Index Cond: (i &lt; 10)
+(3 rows)
+</pre><p>
+ </p><p>
+ Here is an example of using <code class="command">EXPLAIN EXECUTE</code> to
+ display the execution plan for a prepared query:
+
+</p><pre class="programlisting">
+PREPARE query(int, int) AS SELECT sum(bar) FROM test
+ WHERE id &gt; $1 AND id &lt; $2
+ GROUP BY foo;
+
+EXPLAIN ANALYZE EXECUTE query(100, 200);
+
+ QUERY PLAN
+-------------------------------------------------------------------​-----------------------------------------------------
+ HashAggregate (cost=9.54..9.54 rows=1 width=8) (actual time=0.156..0.161 rows=11 loops=1)
+ Group Key: foo
+ -&gt; Index Scan using test_pkey on test (cost=0.29..9.29 rows=50 width=8) (actual time=0.039..0.091 rows=99 loops=1)
+ Index Cond: ((id &gt; $1) AND (id &lt; $2))
+ Planning time: 0.197 ms
+ Execution time: 0.225 ms
+(6 rows)
+</pre><p>
+ </p><p>
+ Of course, the specific numbers shown here depend on the actual
+ contents of the tables involved. Also note that the numbers, and
+ even the selected query strategy, might vary between
+ <span class="productname">PostgreSQL</span> releases due to planner
+ improvements. In addition, the <code class="command">ANALYZE</code> command
+ uses random sampling to estimate data statistics; therefore, it is
+ possible for cost estimates to change after a fresh run of
+ <code class="command">ANALYZE</code>, even if the actual distribution of data
+ in the table has not changed.
+ </p></div><div class="refsect1" id="id-1.9.3.148.12"><h2>Compatibility</h2><p>
+ There is no <code class="command">EXPLAIN</code> statement defined in the SQL standard.
+ </p></div><div class="refsect1" id="id-1.9.3.148.13"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-execute.html" title="EXECUTE">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-fetch.html" title="FETCH">Next</a></td></tr><tr><td width="40%" align="left" valign="top">EXECUTE </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> FETCH</td></tr></table></div></body></html> \ No newline at end of file