Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
sql-explain.html351 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.3 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">3EXPLAIN [ ( <em class="replaceable"><code>option</code></em> [, ...] ) ] <em class="replaceable"><code>statement</code></em>4EXPLAIN [ ANALYZE ] [ VERBOSE ] <em class="replaceable"><code>statement</code></em>5 6<span class="phrase">where <em class="replaceable"><code>option</code></em> can be one of:</span>7 8    ANALYZE [ <em class="replaceable"><code>boolean</code></em> ]9    VERBOSE [ <em class="replaceable"><code>boolean</code></em> ]10    COSTS [ <em class="replaceable"><code>boolean</code></em> ]11    SETTINGS [ <em class="replaceable"><code>boolean</code></em> ]12    GENERIC_PLAN [ <em class="replaceable"><code>boolean</code></em> ]13    BUFFERS [ <em class="replaceable"><code>boolean</code></em> ]14    WAL [ <em class="replaceable"><code>boolean</code></em> ]15    TIMING [ <em class="replaceable"><code>boolean</code></em> ]16    SUMMARY [ <em class="replaceable"><code>boolean</code></em> ]17    FORMAT { TEXT | XML | JSON | YAML }18</pre></div><div class="refsect1" id="id-1.9.3.148.7"><h2>Description</h2><p>19   This command displays the execution plan that the20   <span class="productname">PostgreSQL</span> planner generates for the21   supplied statement.  The execution plan shows how the table(s)22   referenced by the statement will be scanned — by plain sequential scan,23   index scan, etc. — and if multiple tables are referenced, what join24   algorithms will be used to bring together the required rows from25   each input table.26  </p><p>27   The most critical part of the display is the estimated statement execution28   cost, which is the planner's guess at how long it will take to run the29   statement (measured in cost units that are arbitrary, but conventionally30   mean disk page fetches).  Actually two numbers31   are shown: the start-up cost before the first row can be returned, and32   the total cost to return all the rows.  For most queries the total cost33   is what matters, but in contexts such as a subquery in <code class="literal">EXISTS</code>, the planner34   will choose the smallest start-up cost instead of the smallest total cost35   (since the executor will stop after getting one row, anyway).36   Also, if you limit the number of rows to return with a <code class="literal">LIMIT</code> clause,37   the planner makes an appropriate interpolation between the endpoint38   costs to estimate which plan is really the cheapest.39  </p><p>40   The <code class="literal">ANALYZE</code> option causes the statement to be actually41   executed, not only planned.  Then actual run time statistics are added to42   the display, including the total elapsed time expended within each plan43   node (in milliseconds) and the total number of rows it actually returned.44   This is useful for seeing whether the planner's estimates45   are close to reality.46  </p><div class="important"><h3 class="title">Important</h3><p>47    Keep in mind that the statement is actually executed when48    the <code class="literal">ANALYZE</code> option is used.  Although49    <code class="command">EXPLAIN</code> will discard any output that a50    <code class="command">SELECT</code> would return, other side effects of the51    statement will happen as usual.  If you wish to use52    <code class="command">EXPLAIN ANALYZE</code> on an53    <code class="command">INSERT</code>, <code class="command">UPDATE</code>,54    <code class="command">DELETE</code>, <code class="command">MERGE</code>,55    <code class="command">CREATE TABLE AS</code>,56    or <code class="command">EXECUTE</code> statement57    without letting the command affect your data, use this approach:58</p><pre class="programlisting">59BEGIN;60EXPLAIN ANALYZE ...;61ROLLBACK;62</pre><p>63   </p></div><p>64   Only the <code class="literal">ANALYZE</code> and <code class="literal">VERBOSE</code> options65   can be specified, and only in that order, without surrounding the option66   list in parentheses.  Prior to <span class="productname">PostgreSQL</span> 9.0,67   the unparenthesized syntax was the only one supported.  It is expected that68   all new options will be supported only in the parenthesized syntax.69  </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>70      Carry out the command and show actual run times and other statistics.71      This parameter defaults to <code class="literal">FALSE</code>.72     </p></dd><dt><span class="term"><code class="literal">VERBOSE</code></span></dt><dd><p>73      Display additional information regarding the plan.  Specifically, include74      the output column list for each node in the plan tree, schema-qualify75      table and function names, always label variables in expressions with76      their range table alias, and always print the name of each trigger for77      which statistics are displayed.  The query identifier will also be78      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 parameter79      defaults to <code class="literal">FALSE</code>.80     </p></dd><dt><span class="term"><code class="literal">COSTS</code></span></dt><dd><p>81      Include information on the estimated startup and total cost of each82      plan node, as well as the estimated number of rows and the estimated83      width of each row.84      This parameter defaults to <code class="literal">TRUE</code>.85     </p></dd><dt><span class="term"><code class="literal">SETTINGS</code></span></dt><dd><p>86      Include information on configuration parameters.  Specifically, include87      options affecting query planning with value different from the built-in88      default value.  This parameter defaults to <code class="literal">FALSE</code>.89     </p></dd><dt><span class="term"><code class="literal">GENERIC_PLAN</code></span></dt><dd><p>90      Allow the statement to contain parameter placeholders like91      <code class="literal">$1</code>, and generate a generic plan that does not92      depend on the values of those parameters.93      See <a class="link" href="sql-prepare.html" title="PREPARE"><code class="command">PREPARE</code></a>94      for details about generic plans and the types of statement that95      support parameters.96      This parameter cannot be used together with <code class="literal">ANALYZE</code>.97      It defaults to <code class="literal">FALSE</code>.98     </p></dd><dt><span class="term"><code class="literal">BUFFERS</code></span></dt><dd><p>99      Include information on buffer usage. Specifically, include the number of100      shared blocks hit, read, dirtied, and written, the number of local blocks101      hit, read, dirtied, and written, the number of temp blocks read and102      written, and the time spent reading and writing data file blocks and103      temporary file blocks (in milliseconds) if104      <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</a> is enabled.  A105      <span class="emphasis"><em>hit</em></span> means that a read was avoided because the block106      was found already in cache when needed.107      Shared blocks contain data from regular tables and indexes;108      local blocks contain data from temporary tables and indexes;109      while temporary blocks contain short-term working data used in sorts,110      hashes, Materialize plan nodes, and similar cases.111      The number of blocks <span class="emphasis"><em>dirtied</em></span> indicates the number of112      previously unmodified blocks that were changed by this query; while the113      number of blocks <span class="emphasis"><em>written</em></span> indicates the number of114      previously-dirtied blocks evicted from cache by this backend during115      query processing.116      The number of blocks shown for an117      upper-level node includes those used by all its child nodes.  In text118      format, only non-zero values are printed.  This parameter defaults to119      <code class="literal">FALSE</code>.120     </p></dd><dt><span class="term"><code class="literal">WAL</code></span></dt><dd><p>121      Include information on WAL record generation. Specifically, include the122      number of records, number of full page images (fpi) and the amount of WAL123      generated in bytes. In text format, only non-zero values are printed.124      This parameter may only be used when <code class="literal">ANALYZE</code> is also125      enabled.  It defaults to <code class="literal">FALSE</code>.126     </p></dd><dt><span class="term"><code class="literal">TIMING</code></span></dt><dd><p>127      Include actual startup time and time spent in each node in the output.128      The overhead of repeatedly reading the system clock can slow down the129      query significantly on some systems, so it may be useful to set this130      parameter to <code class="literal">FALSE</code> when only actual row counts, and131      not exact times, are needed.  Run time of the entire statement is132      always measured, even when node-level timing is turned off with this133      option.134      This parameter may only be used when <code class="literal">ANALYZE</code> is also135      enabled.  It defaults to <code class="literal">TRUE</code>.136     </p></dd><dt><span class="term"><code class="literal">SUMMARY</code></span></dt><dd><p>137      Include summary information (e.g., totaled timing information) after the138      query plan.  Summary information is included by default when139      <code class="literal">ANALYZE</code> is used but otherwise is not included by140      default, but can be enabled using this option.  Planning time in141      <code class="command">EXPLAIN EXECUTE</code> includes the time required to fetch142      the plan from the cache and the time required for re-planning, if143      necessary.144     </p></dd><dt><span class="term"><code class="literal">FORMAT</code></span></dt><dd><p>145      Specify the output format, which can be TEXT, XML, JSON, or YAML.146      Non-text output contains the same information as the text output147      format, but is easier for programs to parse.  This parameter defaults to148      <code class="literal">TEXT</code>.149     </p></dd><dt><span class="term"><em class="replaceable"><code>boolean</code></em></span></dt><dd><p>150      Specifies whether the selected option should be turned on or off.151      You can write <code class="literal">TRUE</code>, <code class="literal">ON</code>, or152      <code class="literal">1</code> to enable the option, and <code class="literal">FALSE</code>,153      <code class="literal">OFF</code>, or <code class="literal">0</code> to disable it.  The154      <em class="replaceable"><code>boolean</code></em> value can also155      be omitted, in which case <code class="literal">TRUE</code> is assumed.156     </p></dd><dt><span class="term"><em class="replaceable"><code>statement</code></em></span></dt><dd><p>157      Any <code class="command">SELECT</code>, <code class="command">INSERT</code>, <code class="command">UPDATE</code>,158      <code class="command">DELETE</code>, <code class="command">MERGE</code>,159      <code class="command">VALUES</code>, <code class="command">EXECUTE</code>,160      <code class="command">DECLARE</code>, <code class="command">CREATE TABLE AS</code>, or161      <code class="command">CREATE MATERIALIZED VIEW AS</code> statement, whose execution162      plan you wish to see.163     </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.148.9"><h2>Outputs</h2><p>164    The command's result is a textual description of the plan selected165    for the <em class="replaceable"><code>statement</code></em>,166    optionally annotated with execution statistics.167    <a class="xref" href="using-explain.html" title="14.1. Using EXPLAIN">Section 14.1</a> describes the information provided.168   </p></div><div class="refsect1" id="id-1.9.3.148.10"><h2>Notes</h2><p>169   In order to allow the <span class="productname">PostgreSQL</span> query170   planner to make reasonably informed decisions when optimizing171   queries, the <a class="link" href="catalog-pg-statistic.html" title="53.51. pg_statistic"><code class="structname">pg_statistic</code></a>172   data should be up-to-date for all tables used in the query.  Normally173   the <a class="link" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">autovacuum daemon</a> will take care174   of that automatically.  But if a table has recently had substantial175   changes in its contents, you might need to do a manual176   <a class="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></a> rather than wait for autovacuum to catch up177   with the changes.178  </p><p>179   In order to measure the run-time cost of each node in the execution180   plan, the current implementation of <code class="command">EXPLAIN181   ANALYZE</code> adds profiling overhead to query execution.182   As a result, running <code class="command">EXPLAIN ANALYZE</code>183   on a query can sometimes take significantly longer than executing184   the query normally. The amount of overhead depends on the nature of185   the query, as well as the platform being used.  The worst case occurs186   for plan nodes that in themselves require very little time per187   execution, and on machines that have relatively slow operating188   system calls for obtaining the time of day.189  </p></div><div class="refsect1" id="id-1.9.3.148.11"><h2>Examples</h2><p>190   To show the plan for a simple query on a table with a single191   <code class="type">integer</code> column and 10000 rows:192 193</p><pre class="programlisting">194EXPLAIN SELECT * FROM foo;195 196                       QUERY PLAN197---------------------------------------------------------198 Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)199(1 row)200</pre><p>201  </p><p>202  Here is the same query, with JSON output formatting:203</p><pre class="programlisting">204EXPLAIN (FORMAT JSON) SELECT * FROM foo;205           QUERY PLAN206--------------------------------207 [                             +208   {                           +209     "Plan": {                 +210       "Node Type": "Seq Scan",+211       "Relation Name": "foo", +212       "Alias": "foo",         +213       "Startup Cost": 0.00,   +214       "Total Cost": 155.00,   +215       "Plan Rows": 10000,     +216       "Plan Width": 4         +217     }                         +218   }                           +219 ]220(1 row)221</pre><p>222  </p><p>223   If there is an index and we use a query with an indexable224   <code class="literal">WHERE</code> condition, <code class="command">EXPLAIN</code>225   might show a different plan:226 227</p><pre class="programlisting">228EXPLAIN SELECT * FROM foo WHERE i = 4;229 230                         QUERY PLAN231--------------------------------------------------------------232 Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)233   Index Cond: (i = 4)234(2 rows)235</pre><p>236  </p><p>237  Here is the same query, but in YAML format:238</p><pre class="programlisting">239EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';240          QUERY PLAN241-------------------------------242 - Plan:                      +243     Node Type: "Index Scan"  +244     Scan Direction: "Forward"+245     Index Name: "fi"         +246     Relation Name: "foo"     +247     Alias: "foo"             +248     Startup Cost: 0.00       +249     Total Cost: 5.98         +250     Plan Rows: 1             +251     Plan Width: 4            +252     Index Cond: "(i = 4)"253(1 row)254</pre><p>255 256    XML format is left as an exercise for the reader.257  </p><p>258   Here is the same plan with cost estimates suppressed:259 260</p><pre class="programlisting">261EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;262 263        QUERY PLAN264----------------------------265 Index Scan using fi on foo266   Index Cond: (i = 4)267(2 rows)268</pre><p>269  </p><p>270   Here is an example of a query plan for a query using an aggregate271   function:272 273</p><pre class="programlisting">274EXPLAIN SELECT sum(i) FROM foo WHERE i &lt; 10;275 276                             QUERY PLAN277-------------------------------------------------------------------​--278 Aggregate  (cost=23.93..23.93 rows=1 width=4)279   -&gt;  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)280         Index Cond: (i &lt; 10)281(3 rows)282</pre><p>283  </p><p>284   Here is an example of using <code class="command">EXPLAIN EXECUTE</code> to285   display the execution plan for a prepared query:286 287</p><pre class="programlisting">288PREPARE query(int, int) AS SELECT sum(bar) FROM test289    WHERE id &gt; $1 AND id &lt; $2290    GROUP BY foo;291 292EXPLAIN ANALYZE EXECUTE query(100, 200);293 294                                                       QUERY PLAN295-------------------------------------------------------------------​------------------------------------------------------296 HashAggregate  (cost=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10 loops=1)297   Group Key: foo298   Batches: 1  Memory Usage: 24kB299   -&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)300         Index Cond: ((id &gt; 100) AND (id &lt; 200))301 Planning Time: 0.244 ms302 Execution Time: 0.073 ms303(7 rows)304</pre><p>305  </p><p>306   Of course, the specific numbers shown here depend on the actual307   contents of the tables involved.  Also note that the numbers, and308   even the selected query strategy, might vary between309   <span class="productname">PostgreSQL</span> releases due to planner310   improvements. In addition, the <code class="command">ANALYZE</code> command311   uses random sampling to estimate data statistics; therefore, it is312   possible for cost estimates to change after a fresh run of313   <code class="command">ANALYZE</code>, even if the actual distribution of data314   in the table has not changed.315  </p><p>316   Notice that the previous example showed a <span class="quote">“<span class="quote">custom</span>”</span> plan317   for the specific parameter values given in <code class="command">EXECUTE</code>.318   We might also wish to see the generic plan for a parameterized319   query, which can be done with <code class="literal">GENERIC_PLAN</code>:320 321</p><pre class="programlisting">322EXPLAIN (GENERIC_PLAN)323  SELECT sum(bar) FROM test324    WHERE id &gt; $1 AND id &lt; $2325    GROUP BY foo;326 327                                  QUERY PLAN328-------------------------------------------------------------------​------------329 HashAggregate  (cost=26.79..26.89 rows=10 width=12)330   Group Key: foo331   -&gt;  Index Scan using test_pkey on test  (cost=0.29..24.29 rows=500 width=8)332         Index Cond: ((id &gt; $1) AND (id &lt; $2))333(4 rows)334</pre><p>335 336   In this case the parser correctly inferred that <code class="literal">$1</code>337   and <code class="literal">$2</code> should have the same data type338   as <code class="literal">id</code>, so the lack of parameter type information339   from <code class="command">PREPARE</code> was not a problem.  In other cases340   it might be necessary to explicitly specify types for the parameter341   symbols, which can be done by casting them, for example:342 343</p><pre class="programlisting">344EXPLAIN (GENERIC_PLAN)345  SELECT sum(bar) FROM test346    WHERE id &gt; $1::integer AND id &lt; $2::integer347    GROUP BY foo;348</pre><p>349  </p></div><div class="refsect1" id="id-1.9.3.148.12"><h2>Compatibility</h2><p>350   There is no <code class="command">EXPLAIN</code> statement defined in the SQL standard.351  </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.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> FETCH</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai