Debian Codegraph @

doc/src/sgml/ref/explain.sgml

raw
1 <!--
2 doc/src/sgml/ref/explain.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-explain">
7 <indexterm zone="sql-explain">
8 <primary>EXPLAIN</primary>
9 </indexterm>
10
11 <indexterm zone="sql-explain">
12 <primary>prepared statements</primary>
13 <secondary>showing the query plan</secondary>
14 </indexterm>
15
16 <indexterm zone="sql-explain">
17 <primary>cursor</primary>
18 <secondary>showing the query plan</secondary>
19 </indexterm>
20
21 <refmeta>
22 <refentrytitle>EXPLAIN</refentrytitle>
23 <manvolnum>7</manvolnum>
24 <refmiscinfo>SQL - Language Statements</refmiscinfo>
25 </refmeta>
26
27 <refnamediv>
28 <refname>EXPLAIN</refname>
29 <refpurpose>show the execution plan of a statement</refpurpose>
30 </refnamediv>
31
32 <refsynopsisdiv>
33 <synopsis>
34 EXPLAIN [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] <replaceable class="parameter">statement</replaceable>
35
36 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
37
38 ANALYZE [ <replaceable class="parameter">boolean</replaceable> ]
39 VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
40 COSTS [ <replaceable class="parameter">boolean</replaceable> ]
41 SETTINGS [ <replaceable class="parameter">boolean</replaceable> ]
42 GENERIC_PLAN [ <replaceable class="parameter">boolean</replaceable> ]
43 BUFFERS [ <replaceable class="parameter">boolean</replaceable> ]
44 SERIALIZE [ { NONE | TEXT | BINARY } ]
45 WAL [ <replaceable class="parameter">boolean</replaceable> ]
46 TIMING [ <replaceable class="parameter">boolean</replaceable> ]
47 SUMMARY [ <replaceable class="parameter">boolean</replaceable> ]
48 MEMORY [ <replaceable class="parameter">boolean</replaceable> ]
49 FORMAT { TEXT | XML | JSON | YAML }
50 </synopsis>
51 </refsynopsisdiv>
52
53 <refsect1>
54 <title>Description</title>
55
56 <para>
57 This command displays the execution plan that the
58 <productname>PostgreSQL</productname> planner generates for the
59 supplied statement. The execution plan shows how the table(s)
60 referenced by the statement will be scanned &mdash; by plain sequential scan,
61 index scan, etc. &mdash; and if multiple tables are referenced, what join
62 algorithms will be used to bring together the required rows from
63 each input table.
64 </para>
65
66 <para>
67 The most critical part of the display is the estimated statement execution
68 cost, which is the planner's guess at how long it will take to run the
69 statement (measured in cost units that are arbitrary, but conventionally
70 mean disk page fetches). Actually two numbers
71 are shown: the start-up cost before the first row can be returned, and
72 the total cost to return all the rows. For most queries the total cost
73 is what matters, but in contexts such as a subquery in <literal>EXISTS</literal>, the planner
74 will choose the smallest start-up cost instead of the smallest total cost
75 (since the executor will stop after getting one row, anyway).
76 Also, if you limit the number of rows to return with a <literal>LIMIT</literal> clause,
77 the planner makes an appropriate interpolation between the endpoint
78 costs to estimate which plan is really the cheapest.
79 </para>
80
81 <para>
82 The <literal>ANALYZE</literal> option causes the statement to be actually
83 executed, not only planned. Then actual run time statistics are added to
84 the display, including the total elapsed time expended within each plan
85 node (in milliseconds) and the total number of rows it actually returned.
86 This is useful for seeing whether the planner's estimates
87 are close to reality.
88 </para>
89
90 <important>
91 <para>
92 Keep in mind that the statement is actually executed when
93 the <literal>ANALYZE</literal> option is used. Although
94 <command>EXPLAIN</command> will discard any output that a
95 <command>SELECT</command> would return, other side effects of the
96 statement will happen as usual. If you wish to use
97 <command>EXPLAIN ANALYZE</command> on an
98 <command>INSERT</command>, <command>UPDATE</command>,
99 <command>DELETE</command>, <command>MERGE</command>,
100 <command>CREATE TABLE AS</command>,
101 or <command>EXECUTE</command> statement
102 without letting the command affect your data, use this approach:
103 <programlisting>
104 BEGIN;
105 EXPLAIN ANALYZE ...;
106 ROLLBACK;
107 </programlisting>
108 </para>
109 </important>
110 </refsect1>
111
112 <refsect1>
113 <title>Parameters</title>
114
115 <variablelist>
116 <varlistentry>
117 <term><literal>ANALYZE</literal></term>
118 <listitem>
119 <para>
120 Carry out the command and show actual run times and other statistics.
121 This parameter defaults to <literal>FALSE</literal>.
122 </para>
123 </listitem>
124 </varlistentry>
125
126 <varlistentry>
127 <term><literal>VERBOSE</literal></term>
128 <listitem>
129 <para>
130 Display additional information regarding the plan. Specifically, include
131 the output column list for each node in the plan tree, schema-qualify
132 table and function names, always label variables in expressions with
133 their range table alias, and always print the name of each trigger for
134 which statistics are displayed. The query identifier will also be
135 displayed if one has been computed, see <xref
136 linkend="guc-compute-query-id"/> for more details. This parameter
137 defaults to <literal>FALSE</literal>.
138 </para>
139 </listitem>
140 </varlistentry>
141
142 <varlistentry>
143 <term><literal>COSTS</literal></term>
144 <listitem>
145 <para>
146 Include information on the estimated startup and total cost of each
147 plan node, as well as the estimated number of rows and the estimated
148 width of each row.
149 This parameter defaults to <literal>TRUE</literal>.
150 </para>
151 </listitem>
152 </varlistentry>
153
154 <varlistentry>
155 <term><literal>SETTINGS</literal></term>
156 <listitem>
157 <para>
158 Include information on configuration parameters. Specifically, include
159 options affecting query planning with value different from the built-in
160 default value. This parameter defaults to <literal>FALSE</literal>.
161 </para>
162 </listitem>
163 </varlistentry>
164
165 <varlistentry>
166 <term><literal>GENERIC_PLAN</literal></term>
167 <listitem>
168 <para>
169 Allow the statement to contain parameter placeholders like
170 <literal>$1</literal>, and generate a generic plan that does not
171 depend on the values of those parameters.
172 See <link linkend="sql-prepare"><command>PREPARE</command></link>
173 for details about generic plans and the types of statement that
174 support parameters.
175 This parameter cannot be used together with <literal>ANALYZE</literal>.
176 It defaults to <literal>FALSE</literal>.
177 </para>
178 </listitem>
179 </varlistentry>
180
181 <varlistentry>
182 <term><literal>BUFFERS</literal></term>
183 <listitem>
184 <para>
185 Include information on buffer usage. Specifically, include the number of
186 shared blocks hit, read, dirtied, and written, the number of local blocks
187 hit, read, dirtied, and written, the number of temp blocks read and
188 written, and the time spent reading and writing data file blocks, local
189 blocks and temporary file blocks (in milliseconds) if
190 <xref linkend="guc-track-io-timing"/> is enabled. A
191 <emphasis>hit</emphasis> means that a read was avoided because the block
192 was found already in cache when needed.
193 Shared blocks contain data from regular tables and indexes;
194 local blocks contain data from temporary tables and indexes;
195 while temporary blocks contain short-term working data used in sorts,
196 hashes, Materialize plan nodes, and similar cases.
197 The number of blocks <emphasis>dirtied</emphasis> indicates the number of
198 previously unmodified blocks that were changed by this query; while the
199 number of blocks <emphasis>written</emphasis> indicates the number of
200 previously-dirtied blocks evicted from cache by this backend during
201 query processing.
202 The number of blocks shown for an
203 upper-level node includes those used by all its child nodes. In text
204 format, only non-zero values are printed. Buffers information is
205 automatically included when <literal>ANALYZE</literal> is used.
206 </para>
207 </listitem>
208 </varlistentry>
209
210 <varlistentry>
211 <term><literal>SERIALIZE</literal></term>
212 <listitem>
213 <para>
214 Include information on the cost
215 of <firstterm>serializing</firstterm> the query's output data, that
216 is converting it to text or binary format to send to the client.
217 This can be a significant part of the time required for regular
218 execution of the query, if the datatype output functions are
219 expensive or if <acronym>TOAST</acronym>ed values must be fetched
220 from out-of-line storage. <command>EXPLAIN</command>'s default
221 behavior, <literal>SERIALIZE NONE</literal>, does not perform these
222 conversions. If <literal>SERIALIZE TEXT</literal>
223 or <literal>SERIALIZE BINARY</literal> is specified, the appropriate
224 conversions are performed, and the time spent doing so is measured
225 (unless <literal>TIMING OFF</literal> is specified). If
226 the <literal>BUFFERS</literal> option is also specified, then any
227 buffer accesses involved in the conversions are counted too.
228 In no case, however, will <command>EXPLAIN</command> actually send
229 the resulting data to the client; hence network transmission costs
230 cannot be investigated this way.
231 Serialization may only be enabled when <literal>ANALYZE</literal> is
232 also enabled. If <literal>SERIALIZE</literal> is written without an
233 argument, <literal>TEXT</literal> is assumed.
234 </para>
235 </listitem>
236 </varlistentry>
237
238 <varlistentry>
239 <term><literal>WAL</literal></term>
240 <listitem>
241 <para>
242 Include information on WAL record generation. Specifically, include the
243 number of records, number of full page images (fpi), the amount of WAL
244 generated in bytes and the number of times the WAL buffers became full.
245 In text format, only non-zero values are printed.
246 This parameter may only be used when <literal>ANALYZE</literal> is also
247 enabled. It defaults to <literal>FALSE</literal>.
248 </para>
249 </listitem>
250 </varlistentry>
251
252 <varlistentry>
253 <term><literal>TIMING</literal></term>
254 <listitem>
255 <para>
256 Include actual startup time and time spent in each node in the output.
257 The overhead of repeatedly reading the system clock can slow down the
258 query significantly on some systems, so it may be useful to set this
259 parameter to <literal>FALSE</literal> when only actual row counts, and
260 not exact times, are needed. Run time of the entire statement is
261 always measured, even when node-level timing is turned off with this
262 option.
263 This parameter may only be used when <literal>ANALYZE</literal> is also
264 enabled. It defaults to <literal>TRUE</literal>.
265 </para>
266 </listitem>
267 </varlistentry>
268
269 <varlistentry>
270 <term><literal>SUMMARY</literal></term>
271 <listitem>
272 <para>
273 Include summary information (e.g., totaled timing information) after the
274 query plan. Summary information is included by default when
275 <literal>ANALYZE</literal> is used but otherwise is not included by
276 default, but can be enabled using this option. Planning time in
277 <command>EXPLAIN EXECUTE</command> includes the time required to fetch
278 the plan from the cache and the time required for re-planning, if
279 necessary.
280 </para>
281 </listitem>
282 </varlistentry>
283
284 <varlistentry>
285 <term><literal>MEMORY</literal></term>
286 <listitem>
287 <para>
288 Include information on memory consumption by the query planning phase.
289 Specifically, include the precise amount of storage used by planner
290 in-memory structures, as well as total memory considering allocation
291 overhead.
292 This parameter defaults to <literal>FALSE</literal>.
293 </para>
294 </listitem>
295 </varlistentry>
296
297 <varlistentry>
298 <term><literal>FORMAT</literal></term>
299 <listitem>
300 <para>
301 Specify the output format, which can be TEXT, XML, JSON, or YAML.
302 Non-text output contains the same information as the text output
303 format, but is easier for programs to parse. This parameter defaults to
304 <literal>TEXT</literal>.
305 </para>
306 </listitem>
307 </varlistentry>
308
309 <varlistentry>
310 <term><replaceable class="parameter">boolean</replaceable></term>
311 <listitem>
312 <para>
313 Specifies whether the selected option should be turned on or off.
314 You can write <literal>TRUE</literal>, <literal>ON</literal>, or
315 <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
316 <literal>OFF</literal>, or <literal>0</literal> to disable it. The
317 <replaceable class="parameter">boolean</replaceable> value can also
318 be omitted, in which case <literal>TRUE</literal> is assumed.
319 </para>
320 </listitem>
321 </varlistentry>
322
323 <varlistentry>
324 <term><replaceable class="parameter">statement</replaceable></term>
325 <listitem>
326 <para>
327 Any <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>,
328 <command>DELETE</command>, <command>MERGE</command>,
329 <command>VALUES</command>, <command>EXECUTE</command>,
330 <command>DECLARE</command>, <command>CREATE TABLE AS</command>, or
331 <command>CREATE MATERIALIZED VIEW AS</command> statement, whose execution
332 plan you wish to see.
333 </para>
334 </listitem>
335 </varlistentry>
336 </variablelist>
337 </refsect1>
338
339 <refsect1>
340 <title>Outputs</title>
341
342 <para>
343 The command's result is a textual description of the plan selected
344 for the <replaceable class="parameter">statement</replaceable>,
345 optionally annotated with execution statistics.
346 <xref linkend="using-explain"/> describes the information provided.
347 </para>
348 </refsect1>
349
350 <refsect1>
351 <title>Notes</title>
352
353 <para>
354 In order to allow the <productname>PostgreSQL</productname> query
355 planner to make reasonably informed decisions when optimizing
356 queries, the <link
357 linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>
358 data should be up-to-date for all tables used in the query. Normally
359 the <link linkend="autovacuum">autovacuum daemon</link> will take care
360 of that automatically. But if a table has recently had substantial
361 changes in its contents, you might need to do a manual
362 <link linkend="sql-analyze"><command>ANALYZE</command></link> rather than wait for autovacuum to catch up
363 with the changes.
364 </para>
365
366 <para>
367 In order to measure the run-time cost of each node in the execution
368 plan, the current implementation of <command>EXPLAIN
369 ANALYZE</command> adds profiling overhead to query execution.
370 As a result, running <command>EXPLAIN ANALYZE</command>
371 on a query can sometimes take significantly longer than executing
372 the query normally. The amount of overhead depends on the nature of
373 the query, as well as the platform being used. The worst case occurs
374 for plan nodes that in themselves require very little time per
375 execution, and on machines that have relatively slow operating
376 system calls for obtaining the time of day.
377 </para>
378 </refsect1>
379
380 <refsect1>
381 <title>Examples</title>
382
383 <para>
384 To show the plan for a simple query on a table with a single
385 <type>integer</type> column and 10000 rows:
386
387 <programlisting>
388 EXPLAIN SELECT * FROM foo;
389
390 QUERY PLAN
391 ---------------------------------------------------------
392 Seq Scan on foo (cost=0.00..155.00 rows=10000 width=4)
393 (1 row)
394 </programlisting>
395 </para>
396
397 <para>
398 Here is the same query, with JSON output formatting:
399 <programlisting>
400 EXPLAIN (FORMAT JSON) SELECT * FROM foo;
401 QUERY PLAN
402 --------------------------------
403 [ +
404 { +
405 "Plan": { +
406 "Node Type": "Seq Scan",+
407 "Relation Name": "foo", +
408 "Alias": "foo", +
409 "Startup Cost": 0.00, +
410 "Total Cost": 155.00, +
411 "Plan Rows": 10000, +
412 "Plan Width": 4 +
413 } +
414 } +
415 ]
416 (1 row)
417 </programlisting>
418 </para>
419
420 <para>
421 If there is an index and we use a query with an indexable
422 <literal>WHERE</literal> condition, <command>EXPLAIN</command>
423 might show a different plan:
424
425 <programlisting>
426 EXPLAIN SELECT * FROM foo WHERE i = 4;
427
428 QUERY PLAN
429 --------------------------------------------------------------
430 Index Scan using fi on foo (cost=0.00..5.98 rows=1 width=4)
431 Index Cond: (i = 4)
432 (2 rows)
433 </programlisting>
434 </para>
435
436 <para>
437 Here is the same query, but in YAML format:
438 <programlisting>
439 EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';
440 QUERY PLAN
441 -------------------------------
442 - Plan: +
443 Node Type: "Index Scan" +
444 Scan Direction: "Forward"+
445 Index Name: "fi" +
446 Relation Name: "foo" +
447 Alias: "foo" +
448 Startup Cost: 0.00 +
449 Total Cost: 5.98 +
450 Plan Rows: 1 +
451 Plan Width: 4 +
452 Index Cond: "(i = 4)"
453 (1 row)
454 </programlisting>
455
456 XML format is left as an exercise for the reader.
457 </para>
458 <para>
459 Here is the same plan with cost estimates suppressed:
460
461 <programlisting>
462 EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;
463
464 QUERY PLAN
465 ----------------------------
466 Index Scan using fi on foo
467 Index Cond: (i = 4)
468 (2 rows)
469 </programlisting>
470 </para>
471
472 <para>
473 Here is an example of a query plan for a query using an aggregate
474 function:
475
476 <programlisting>
477 EXPLAIN SELECT sum(i) FROM foo WHERE i &lt; 10;
478
479 QUERY PLAN
480 -------------------------------------------------------------------&zwsp;--
481 Aggregate (cost=23.93..23.93 rows=1 width=4)
482 -&gt; Index Scan using fi on foo (cost=0.00..23.92 rows=6 width=4)
483 Index Cond: (i &lt; 10)
484 (3 rows)
485 </programlisting>
486 </para>
487
488 <para>
489 Here is an example of using <command>EXPLAIN EXECUTE</command> to
490 display the execution plan for a prepared query:
491
492 <programlisting>
493 PREPARE query(int, int) AS SELECT sum(bar) FROM test
494 WHERE id &gt; $1 AND id &lt; $2
495 GROUP BY foo;
496
497 EXPLAIN ANALYZE EXECUTE query(100, 200);
498
499 QUERY PLAN
500 -------------------------------------------------------------------&zwsp;------------------------------------------------------
501 HashAggregate (cost=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10.00 loops=1)
502 Group Key: foo
503 Batches: 1 Memory Usage: 24kB
504 Buffers: shared hit=4
505 -&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.00 loops=1)
506 Index Cond: ((id &gt; 100) AND (id &lt; 200))
507 Index Searches: 1
508 Buffers: shared hit=4
509 Planning Time: 0.244 ms
510 Execution Time: 0.073 ms
511 (10 rows)
512 </programlisting>
513 </para>
514
515 <para>
516 Of course, the specific numbers shown here depend on the actual
517 contents of the tables involved. Also note that the numbers, and
518 even the selected query strategy, might vary between
519 <productname>PostgreSQL</productname> releases due to planner
520 improvements. In addition, the <command>ANALYZE</command> command
521 uses random sampling to estimate data statistics; therefore, it is
522 possible for cost estimates to change after a fresh run of
523 <command>ANALYZE</command>, even if the actual distribution of data
524 in the table has not changed.
525 </para>
526
527 <para>
528 Notice that the previous example showed a <quote>custom</quote> plan
529 for the specific parameter values given in <command>EXECUTE</command>.
530 We might also wish to see the generic plan for a parameterized
531 query, which can be done with <literal>GENERIC_PLAN</literal>:
532
533 <programlisting>
534 EXPLAIN (GENERIC_PLAN)
535 SELECT sum(bar) FROM test
536 WHERE id &gt; $1 AND id &lt; $2
537 GROUP BY foo;
538
539 QUERY PLAN
540 -------------------------------------------------------------------&zwsp;------------
541 HashAggregate (cost=26.79..26.89 rows=10 width=12)
542 Group Key: foo
543 -&gt; Index Scan using test_pkey on test (cost=0.29..24.29 rows=500 width=8)
544 Index Cond: ((id &gt; $1) AND (id &lt; $2))
545 (4 rows)
546 </programlisting>
547
548 In this case the parser correctly inferred that <literal>$1</literal>
549 and <literal>$2</literal> should have the same data type
550 as <literal>id</literal>, so the lack of parameter type information
551 from <command>PREPARE</command> was not a problem. In other cases
552 it might be necessary to explicitly specify types for the parameter
553 symbols, which can be done by casting them, for example:
554
555 <programlisting>
556 EXPLAIN (GENERIC_PLAN)
557 SELECT sum(bar) FROM test
558 WHERE id &gt; $1::integer AND id &lt; $2::integer
559 GROUP BY foo;
560 </programlisting>
561 </para>
562 </refsect1>
563
564 <refsect1>
565 <title>Compatibility</title>
566
567 <para>
568 There is no <command>EXPLAIN</command> statement defined in the SQL standard.
569 </para>
570
571 <para>
572 The following syntax was used before <productname>PostgreSQL</productname>
573 version 9.0 and is still supported:
574 <synopsis>
575 EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="parameter">statement</replaceable>
576 </synopsis>
577 Note that in this syntax, the options must be specified in exactly the order
578 shown.
579 </para>
580 </refsect1>
581
582 <refsect1>
583 <title>See Also</title>
584
585 <simplelist type="inline">
586 <member><xref linkend="sql-analyze"/></member>
587 </simplelist>
588 </refsect1>
589 </refentry>