codekingpro/portable-devtools
115k
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 < 10;275 276 QUERY PLAN277---------------------------------------------------------------------278 Aggregate (cost=23.93..23.93 rows=1 width=4)279 -> Index Scan using fi on foo (cost=0.00..23.92 rows=6 width=4)280 Index Cond: (i < 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 > $1 AND id < $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 -> 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 > 100) AND (id < 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 > $1 AND id < $2325 GROUP BY foo;326 327 QUERY PLAN328-------------------------------------------------------------------------------329 HashAggregate (cost=26.79..26.89 rows=10 width=12)330 Group Key: foo331 -> Index Scan using test_pkey on test (cost=0.29..24.29 rows=500 width=8)332 Index Cond: ((id > $1) AND (id < $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 > $1::integer AND id < $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>