diff options
Diffstat (limited to 'doc/src/sgml/html/sql-explain.html')
| -rw-r--r-- | doc/src/sgml/html/sql-explain.html | 351 |
1 files changed, 351 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..4d8f092 --- /dev/null +++ b/doc/src/sgml/html/sql-explain.html @@ -0,0 +1,351 @@ +<?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 16.2 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> ] + GENERIC_PLAN [ <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">GENERIC_PLAN</code></span></dt><dd><p> + Allow the statement to contain parameter placeholders like + <code class="literal">$1</code>, and generate a generic plan that does not + depend on the values of those parameters. + See <a class="link" href="sql-prepare.html" title="PREPARE"><code class="command">PREPARE</code></a> + for details about generic plans and the types of statement that + support parameters. + This parameter cannot be used together with <code class="literal">ANALYZE</code>. + It 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. This parameter 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 < 10; + + 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) +</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 > $1 AND id < $2 + GROUP BY foo; + +EXPLAIN ANALYZE EXECUTE query(100, 200); + + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- + HashAggregate (cost=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10 loops=1) + Group Key: foo + Batches: 1 Memory Usage: 24kB + -> 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) + Index Cond: ((id > 100) AND (id < 200)) + Planning Time: 0.244 ms + Execution Time: 0.073 ms +(7 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><p> + Notice that the previous example showed a <span class="quote">“<span class="quote">custom</span>”</span> plan + for the specific parameter values given in <code class="command">EXECUTE</code>. + We might also wish to see the generic plan for a parameterized + query, which can be done with <code class="literal">GENERIC_PLAN</code>: + +</p><pre class="programlisting"> +EXPLAIN (GENERIC_PLAN) + SELECT sum(bar) FROM test + WHERE id > $1 AND id < $2 + GROUP BY foo; + + QUERY PLAN +------------------------------------------------------------------------------- + HashAggregate (cost=26.79..26.89 rows=10 width=12) + Group Key: foo + -> Index Scan using test_pkey on test (cost=0.29..24.29 rows=500 width=8) + Index Cond: ((id > $1) AND (id < $2)) +(4 rows) +</pre><p> + + In this case the parser correctly inferred that <code class="literal">$1</code> + and <code class="literal">$2</code> should have the same data type + as <code class="literal">id</code>, so the lack of parameter type information + from <code class="command">PREPARE</code> was not a problem. In other cases + it might be necessary to explicitly specify types for the parameter + symbols, which can be done by casting them, for example: + +</p><pre class="programlisting"> +EXPLAIN (GENERIC_PLAN) + SELECT sum(bar) FROM test + WHERE id > $1::integer AND id < $2::integer + GROUP BY foo; +</pre><p> + </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 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> FETCH</td></tr></table></div></body></html>
\ No newline at end of file |