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>14.1. Using 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="performance-tips.html" title="Chapter 14. Performance Tips" /><link rel="next" href="planner-stats.html" title="14.2. Statistics Used by the Planner" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">14.1. Using <code class="command">EXPLAIN</code></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="performance-tips.html" title="Chapter 14. Performance Tips">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="performance-tips.html" title="Chapter 14. Performance Tips">Up</a></td><th width="60%" align="center">Chapter 14. Performance Tips</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="planner-stats.html" title="14.2. Statistics Used by the Planner">Next</a></td></tr></table><hr /></div><div class="sect1" id="USING-EXPLAIN"><div class="titlepage"><div><div><h2 class="title" style="clear: both">14.1. Using <code class="command">EXPLAIN</code> <a href="#USING-EXPLAIN" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="using-explain.html#USING-EXPLAIN-BASICS">14.1.1. <code class="command">EXPLAIN</code> Basics</a></span></dt><dt><span class="sect2"><a href="using-explain.html#USING-EXPLAIN-ANALYZE">14.1.2. <code class="command">EXPLAIN ANALYZE</code></a></span></dt><dt><span class="sect2"><a href="using-explain.html#USING-EXPLAIN-CAVEATS">14.1.3. Caveats</a></span></dt></dl></div><a id="id-1.5.13.4.2" class="indexterm"></a><a id="id-1.5.13.4.3" class="indexterm"></a><p>
3 <span class="productname">PostgreSQL</span> devises a <em class="firstterm">query
4 plan</em> for each query it receives. Choosing the right
5 plan to match the query structure and the properties of the data
6 is absolutely critical for good performance, so the system includes
7 a complex <em class="firstterm">planner</em> that tries to choose good plans.
8 You can use the <a class="link" href="sql-explain.html" title="EXPLAIN"><code class="command">EXPLAIN</code></a> command
9 to see what query plan the planner creates for any query.
10 Plan-reading is an art that requires some experience to master,
11 but this section attempts to cover the basics.
13 Examples in this section are drawn from the regression test database
14 after doing a <code class="command">VACUUM ANALYZE</code>, using v18 development sources.
15 You should be able to get similar results if you try the examples
16 yourself, but your estimated costs and row counts might vary slightly
17 because <code class="command">ANALYZE</code>'s statistics are random samples rather
18 than exact, and because costs are inherently somewhat platform-dependent.
20 The examples use <code class="command">EXPLAIN</code>'s default <span class="quote">“<span class="quote">text</span>”</span> output
21 format, which is compact and convenient for humans to read.
22 If you want to feed <code class="command">EXPLAIN</code>'s output to a program for further
23 analysis, you should use one of its machine-readable output formats
24 (XML, JSON, or YAML) instead.
25 </p><div class="sect2" id="USING-EXPLAIN-BASICS"><div class="titlepage"><div><div><h3 class="title">14.1.1. <code class="command">EXPLAIN</code> Basics <a href="#USING-EXPLAIN-BASICS" class="id_link">#</a></h3></div></div></div><p>
26 The structure of a query plan is a tree of <em class="firstterm">plan nodes</em>.
27 Nodes at the bottom level of the tree are scan nodes: they return raw rows
28 from a table. There are different types of scan nodes for different
29 table access methods: sequential scans, index scans, and bitmap index
30 scans. There are also non-table row sources, such as <code class="literal">VALUES</code>
31 clauses and set-returning functions in <code class="literal">FROM</code>, which have their
33 If the query requires joining, aggregation, sorting, or other
34 operations on the raw rows, then there will be additional nodes
35 above the scan nodes to perform these operations. Again,
36 there is usually more than one possible way to do these operations,
37 so different node types can appear here too. The output
38 of <code class="command">EXPLAIN</code> has one line for each node in the plan
39 tree, showing the basic node type plus the cost estimates that the planner
40 made for the execution of that plan node. Additional lines might appear,
41 indented from the node's summary line,
42 to show additional properties of the node.
43 The very first line (the summary line for the topmost
44 node) has the estimated total execution cost for the plan; it is this
45 number that the planner seeks to minimize.
47 Here is a trivial example, just to show what the output looks like:
49 </p><pre class="screen">
50 EXPLAIN SELECT * FROM tenk1;
53 -------------------------------------------------------------
54 Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
57 Since this query has no <code class="literal">WHERE</code> clause, it must scan all the
58 rows of the table, so the planner has chosen to use a simple sequential
59 scan plan. The numbers that are quoted in parentheses are (left
62 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
63 Estimated start-up cost. This is the time expended before the output
64 phase can begin, e.g., time to do the sorting in a sort node.
65 </p></li><li class="listitem"><p>
66 Estimated total cost. This is stated on the assumption that the plan
67 node is run to completion, i.e., all available rows are retrieved.
68 In practice a node's parent node might stop short of reading all
69 available rows (see the <code class="literal">LIMIT</code> example below).
70 </p></li><li class="listitem"><p>
71 Estimated number of rows output by this plan node. Again, the node
72 is assumed to be run to completion.
73 </p></li><li class="listitem"><p>
74 Estimated average width of rows output by this plan node (in bytes).
75 </p></li></ul></div><p>
77 The costs are measured in arbitrary units determined by the planner's
78 cost parameters (see <a class="xref" href="runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS" title="19.7.2. Planner Cost Constants">Section 19.7.2</a>).
79 Traditional practice is to measure the costs in units of disk page
80 fetches; that is, <a class="xref" href="runtime-config-query.html#GUC-SEQ-PAGE-COST">seq_page_cost</a> is conventionally
81 set to <code class="literal">1.0</code> and the other cost parameters are set relative
82 to that. The examples in this section are run with the default cost
85 It's important to understand that the cost of an upper-level node includes
86 the cost of all its child nodes. It's also important to realize that
87 the cost only reflects things that the planner cares about.
88 In particular, the cost does not consider the time spent to convert
89 output values to text form or to transmit them to the client, which
90 could be important factors in the real elapsed time; but the planner
91 ignores those costs because it cannot change them by altering the
92 plan. (Every correct plan will output the same row set, we trust.)
94 The <code class="literal">rows</code> value is a little tricky because it is
95 not the number of rows processed or scanned by the
96 plan node, but rather the number emitted by the node. This is often
97 less than the number scanned, as a result of filtering by any
98 <code class="literal">WHERE</code>-clause conditions that are being applied at the node.
99 Ideally the top-level rows estimate will approximate the number of rows
100 actually returned, updated, or deleted by the query.
102 Returning to our example:
104 </p><pre class="screen">
105 EXPLAIN SELECT * FROM tenk1;
108 -------------------------------------------------------------
109 Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
112 These numbers are derived very straightforwardly. If you do:
114 </p><pre class="programlisting">
115 SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
118 you will find that <code class="classname">tenk1</code> has 345 disk
119 pages and 10000 rows. The estimated cost is computed as (disk pages read *
120 <a class="xref" href="runtime-config-query.html#GUC-SEQ-PAGE-COST">seq_page_cost</a>) + (rows scanned *
121 <a class="xref" href="runtime-config-query.html#GUC-CPU-TUPLE-COST">cpu_tuple_cost</a>). By default,
122 <code class="varname">seq_page_cost</code> is 1.0 and <code class="varname">cpu_tuple_cost</code> is 0.01,
123 so the estimated cost is (345 * 1.0) + (10000 * 0.01) = 445.
125 Now let's modify the query to add a <code class="literal">WHERE</code> condition:
127 </p><pre class="screen">
128 EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
131 ------------------------------------------------------------
132 Seq Scan on tenk1 (cost=0.00..470.00 rows=7000 width=244)
133 Filter: (unique1 < 7000)
136 Notice that the <code class="command">EXPLAIN</code> output shows the <code class="literal">WHERE</code>
137 clause being applied as a <span class="quote">“<span class="quote">filter</span>”</span> condition attached to the Seq
138 Scan plan node. This means that
139 the plan node checks the condition for each row it scans, and outputs
140 only the ones that pass the condition.
141 The estimate of output rows has been reduced because of the
142 <code class="literal">WHERE</code> clause.
143 However, the scan will still have to visit all 10000 rows, so the cost
144 hasn't decreased; in fact it has gone up a bit (by 10000 * <a class="xref" href="runtime-config-query.html#GUC-CPU-OPERATOR-COST">cpu_operator_cost</a>, to be exact) to reflect the extra CPU
145 time spent checking the <code class="literal">WHERE</code> condition.
147 The actual number of rows this query would select is 7000, but the <code class="literal">rows</code>
148 estimate is only approximate. If you try to duplicate this experiment,
149 you may well get a slightly different estimate; moreover, it can
150 change after each <code class="command">ANALYZE</code> command, because the
151 statistics produced by <code class="command">ANALYZE</code> are taken from a
152 randomized sample of the table.
154 Now, let's make the condition more restrictive:
156 </p><pre class="screen">
157 EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
160 ------------------------------------------------------------------------------
161 Bitmap Heap Scan on tenk1 (cost=5.06..224.98 rows=100 width=244)
162 Recheck Cond: (unique1 < 100)
163 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
164 Index Cond: (unique1 < 100)
167 Here the planner has decided to use a two-step plan: the child plan
168 node visits an index to find the locations of rows matching the index
169 condition, and then the upper plan node actually fetches those rows
170 from the table itself. Fetching rows separately is much more
171 expensive than reading them sequentially, but because not all the pages
172 of the table have to be visited, this is still cheaper than a sequential
173 scan. (The reason for using two plan levels is that the upper plan
174 node sorts the row locations identified by the index into physical order
175 before reading them, to minimize the cost of separate fetches.
176 The <span class="quote">“<span class="quote">bitmap</span>”</span> mentioned in the node names is the mechanism that
179 Now let's add another condition to the <code class="literal">WHERE</code> clause:
181 </p><pre class="screen">
182 EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';
185 ------------------------------------------------------------------------------
186 Bitmap Heap Scan on tenk1 (cost=5.04..225.20 rows=1 width=244)
187 Recheck Cond: (unique1 < 100)
188 Filter: (stringu1 = 'xxx'::name)
189 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
190 Index Cond: (unique1 < 100)
193 The added condition <code class="literal">stringu1 = 'xxx'</code> reduces the
194 output row count estimate, but not the cost because we still have to visit
195 the same set of rows. That's because the <code class="literal">stringu1</code> clause
196 cannot be applied as an index condition, since this index is only on
197 the <code class="literal">unique1</code> column. Instead it is applied as a filter on
198 the rows retrieved using the index. Thus the cost has actually gone up
199 slightly to reflect this extra checking.
201 In some cases the planner will prefer a <span class="quote">“<span class="quote">simple</span>”</span> index scan plan:
203 </p><pre class="screen">
204 EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;
207 -----------------------------------------------------------------------------
208 Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244)
209 Index Cond: (unique1 = 42)
212 In this type of plan the table rows are fetched in index order, which
213 makes them even more expensive to read, but there are so few that the
214 extra cost of sorting the row locations is not worth it. You'll most
215 often see this plan type for queries that fetch just a single row. It's
216 also often used for queries that have an <code class="literal">ORDER BY</code> condition
217 that matches the index order, because then no extra sorting step is needed
218 to satisfy the <code class="literal">ORDER BY</code>. In this example, adding
219 <code class="literal">ORDER BY unique1</code> would use the same plan because the
220 index already implicitly provides the requested ordering.
222 The planner may implement an <code class="literal">ORDER BY</code> clause in several
223 ways. The above example shows that such an ordering clause may be
224 implemented implicitly. The planner may also add an explicit
225 <code class="literal">Sort</code> step:
227 </p><pre class="screen">
228 EXPLAIN SELECT * FROM tenk1 ORDER BY unique1;
231 -------------------------------------------------------------------
232 Sort (cost=1109.39..1134.39 rows=10000 width=244)
234 -> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
237 If a part of the plan guarantees an ordering on a prefix of the
238 required sort keys, then the planner may instead decide to use an
239 <code class="literal">Incremental Sort</code> step:
241 </p><pre class="screen">
242 EXPLAIN SELECT * FROM tenk1 ORDER BY hundred, ten LIMIT 100;
245 ------------------------------------------------------------------------------------------------
246 Limit (cost=19.35..39.49 rows=100 width=244)
247 -> Incremental Sort (cost=19.35..2033.39 rows=10000 width=244)
248 Sort Key: hundred, ten
249 Presorted Key: hundred
250 -> Index Scan using tenk1_hundred on tenk1 (cost=0.29..1574.20 rows=10000 width=244)
253 Compared to regular sorts, sorting incrementally allows returning tuples
254 before the entire result set has been sorted, which particularly enables
255 optimizations with <code class="literal">LIMIT</code> queries. It may also reduce
256 memory usage and the likelihood of spilling sorts to disk, but it comes at
257 the cost of the increased overhead of splitting the result set into multiple
260 If there are separate indexes on several of the columns referenced
261 in <code class="literal">WHERE</code>, the planner might choose to use an AND or OR
262 combination of the indexes:
264 </p><pre class="screen">
265 EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
268 -------------------------------------------------------------------------------------
269 Bitmap Heap Scan on tenk1 (cost=25.07..60.11 rows=10 width=244)
270 Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
271 -> BitmapAnd (cost=25.07..25.07 rows=10 width=0)
272 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
273 Index Cond: (unique1 < 100)
274 -> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0)
275 Index Cond: (unique2 > 9000)
278 But this requires visiting both indexes, so it's not necessarily a win
279 compared to using just one index and treating the other condition as
280 a filter. If you vary the ranges involved you'll see the plan change
283 Here is an example showing the effects of <code class="literal">LIMIT</code>:
285 </p><pre class="screen">
286 EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;
289 -------------------------------------------------------------------------------------
290 Limit (cost=0.29..14.28 rows=2 width=244)
291 -> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..70.27 rows=10 width=244)
292 Index Cond: (unique2 > 9000)
293 Filter: (unique1 < 100)
296 This is the same query as above, but we added a <code class="literal">LIMIT</code> so that
297 not all the rows need be retrieved, and the planner changed its mind about
298 what to do. Notice that the total cost and row count of the Index Scan
299 node are shown as if it were run to completion. However, the Limit node
300 is expected to stop after retrieving only a fifth of those rows, so its
301 total cost is only a fifth as much, and that's the actual estimated cost
302 of the query. This plan is preferred over adding a Limit node to the
303 previous plan because the Limit could not avoid paying the startup cost
304 of the bitmap scan, so the total cost would be something over 25 units
307 Let's try joining two tables, using the columns we have been discussing:
309 </p><pre class="screen">
311 FROM tenk1 t1, tenk2 t2
312 WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;
315 --------------------------------------------------------------------------------------
316 Nested Loop (cost=4.65..118.50 rows=10 width=488)
317 -> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244)
318 Recheck Cond: (unique1 < 10)
319 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)
320 Index Cond: (unique1 < 10)
321 -> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.90 rows=1 width=244)
322 Index Cond: (unique2 = t1.unique2)
325 In this plan, we have a nested-loop join node with two table scans as
326 inputs, or children. The indentation of the node summary lines reflects
327 the plan tree structure. The join's first, or <span class="quote">“<span class="quote">outer</span>”</span>, child
328 is a bitmap scan similar to those we saw before. Its cost and row count
329 are the same as we'd get from <code class="literal">SELECT ... WHERE unique1 < 10</code>
331 applying the <code class="literal">WHERE</code> clause <code class="literal">unique1 < 10</code>
333 The <code class="literal">t1.unique2 = t2.unique2</code> clause is not relevant yet,
334 so it doesn't affect the row count of the outer scan. The nested-loop
335 join node will run its second,
336 or <span class="quote">“<span class="quote">inner</span>”</span> child once for each row obtained from the outer child.
337 Column values from the current outer row can be plugged into the inner
338 scan; here, the <code class="literal">t1.unique2</code> value from the outer row is available,
339 so we get a plan and costs similar to what we saw above for a simple
340 <code class="literal">SELECT ... WHERE t2.unique2 = <em class="replaceable"><code>constant</code></em></code> case.
341 (The estimated cost is actually a bit lower than what was seen above,
342 as a result of caching that's expected to occur during the repeated
343 index scans on <code class="literal">t2</code>.) The
344 costs of the loop node are then set on the basis of the cost of the outer
345 scan, plus one repetition of the inner scan for each outer row (10 * 7.90,
346 here), plus a little CPU time for join processing.
348 In this example the join's output row count is the same as the product
349 of the two scans' row counts, but that's not true in all cases because
350 there can be additional <code class="literal">WHERE</code> clauses that mention both tables
351 and so can only be applied at the join point, not to either input scan.
354 </p><pre class="screen">
356 FROM tenk1 t1, tenk2 t2
357 WHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;
360 ---------------------------------------------------------------------------------------------
361 Nested Loop (cost=4.65..49.36 rows=33 width=488)
362 Join Filter: (t1.hundred < t2.hundred)
363 -> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244)
364 Recheck Cond: (unique1 < 10)
365 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)
366 Index Cond: (unique1 < 10)
367 -> Materialize (cost=0.29..8.51 rows=10 width=244)
368 -> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..8.46 rows=10 width=244)
369 Index Cond: (unique2 < 10)
372 The condition <code class="literal">t1.hundred < t2.hundred</code> can't be
373 tested in the <code class="literal">tenk2_unique2</code> index, so it's applied at the
374 join node. This reduces the estimated output row count of the join node,
375 but does not change either input scan.
377 Notice that here the planner has chosen to <span class="quote">“<span class="quote">materialize</span>”</span> the inner
378 relation of the join, by putting a Materialize plan node atop it. This
379 means that the <code class="literal">t2</code> index scan will be done just once, even
380 though the nested-loop join node needs to read that data ten times, once
381 for each row from the outer relation. The Materialize node saves the data
382 in memory as it's read, and then returns the data from memory on each
385 When dealing with outer joins, you might see join plan nodes with both
386 <span class="quote">“<span class="quote">Join Filter</span>”</span> and plain <span class="quote">“<span class="quote">Filter</span>”</span> conditions attached.
387 Join Filter conditions come from the outer join's <code class="literal">ON</code> clause,
388 so a row that fails the Join Filter condition could still get emitted as
389 a null-extended row. But a plain Filter condition is applied after the
390 outer-join rules and so acts to remove rows unconditionally. In an inner
391 join there is no semantic difference between these types of filters.
393 If we change the query's selectivity a bit, we might get a very different
396 </p><pre class="screen">
398 FROM tenk1 t1, tenk2 t2
399 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
402 ------------------------------------------------------------------------------------------
403 Hash Join (cost=226.23..709.73 rows=100 width=488)
404 Hash Cond: (t2.unique2 = t1.unique2)
405 -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)
406 -> Hash (cost=224.98..224.98 rows=100 width=244)
407 -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)
408 Recheck Cond: (unique1 < 100)
409 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
410 Index Cond: (unique1 < 100)
413 Here, the planner has chosen to use a hash join, in which rows of one
414 table are entered into an in-memory hash table, after which the other
415 table is scanned and the hash table is probed for matches to each row.
416 Again note how the indentation reflects the plan structure: the bitmap
417 scan on <code class="literal">tenk1</code> is the input to the Hash node, which constructs
418 the hash table. That's then returned to the Hash Join node, which reads
419 rows from its outer child plan and searches the hash table for each one.
421 Another possible type of join is a merge join, illustrated here:
423 </p><pre class="screen">
425 FROM tenk1 t1, onek t2
426 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
429 ------------------------------------------------------------------------------------------
430 Merge Join (cost=0.56..233.49 rows=10 width=488)
431 Merge Cond: (t1.unique2 = t2.unique2)
432 -> Index Scan using tenk1_unique2 on tenk1 t1 (cost=0.29..643.28 rows=100 width=244)
433 Filter: (unique1 < 100)
434 -> Index Scan using onek_unique2 on onek t2 (cost=0.28..166.28 rows=1000 width=244)
437 Merge join requires its input data to be sorted on the join keys. In this
438 example each input is sorted by using an index scan to visit the rows
439 in the correct order; but a sequential scan and sort could also be used.
440 (Sequential-scan-and-sort frequently beats an index scan for sorting many rows,
441 because of the nonsequential disk access required by the index scan.)
443 One way to look at variant plans is to force the planner to disregard
444 whatever strategy it thought was the cheapest, using the enable/disable
445 flags described in <a class="xref" href="runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE" title="19.7.1. Planner Method Configuration">Section 19.7.1</a>.
446 (This is a crude tool, but useful. See
447 also <a class="xref" href="explicit-joins.html" title="14.3. Controlling the Planner with Explicit JOIN Clauses">Section 14.3</a>.)
448 For example, if we're unconvinced that merge join is the best join
449 type for the previous example, we could try
451 </p><pre class="screen">
452 SET enable_mergejoin = off;
455 FROM tenk1 t1, onek t2
456 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
459 ------------------------------------------------------------------------------------------
460 Hash Join (cost=226.23..344.08 rows=10 width=488)
461 Hash Cond: (t2.unique2 = t1.unique2)
462 -> Seq Scan on onek t2 (cost=0.00..114.00 rows=1000 width=244)
463 -> Hash (cost=224.98..224.98 rows=100 width=244)
464 -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)
465 Recheck Cond: (unique1 < 100)
466 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
467 Index Cond: (unique1 < 100)
470 which shows that the planner thinks that hash join would be nearly 50%
471 more expensive than merge join for this case.
472 Of course, the next question is whether it's right about that.
473 We can investigate that using <code class="command">EXPLAIN ANALYZE</code>, as
474 discussed <a class="link" href="using-explain.html#USING-EXPLAIN-ANALYZE" title="14.1.2. EXPLAIN ANALYZE">below</a>.
476 When using the enable/disable flags to disable plan node types, many of
477 the flags only discourage the use of the corresponding plan node and don't
478 outright disallow the planner's ability to use the plan node type. This
479 is by design so that the planner still maintains the ability to form a
480 plan for a given query. When the resulting plan contains a disabled node,
481 the <code class="command">EXPLAIN</code> output will indicate this fact.
483 </p><pre class="screen">
484 SET enable_seqscan = off;
485 EXPLAIN SELECT * FROM unit;
488 ---------------------------------------------------------
489 Seq Scan on unit (cost=0.00..21.30 rows=1130 width=44)
493 Because the <code class="literal">unit</code> table has no indexes, there is no
494 other means to read the table data, so the sequential scan is the only
495 option available to the query planner.
497 <a id="id-1.5.13.4.7.31.1" class="indexterm"></a>
498 Some query plans involve <em class="firstterm">subplans</em>, which arise
499 from sub-<code class="literal">SELECT</code>s in the original query. Such
500 queries can sometimes be transformed into ordinary join plans, but
501 when they cannot be, we get plans like:
503 </p><pre class="screen">
504 EXPLAIN VERBOSE SELECT unique1
506 WHERE t.ten < ALL (SELECT o.ten FROM onek o WHERE o.four = t.four);
509 -------------------------------------------------------------------------
510 Seq Scan on public.tenk1 t (cost=0.00..586095.00 rows=5000 width=4)
512 Filter: (ALL (t.ten < (SubPlan 1).col1))
514 -> Seq Scan on public.onek o (cost=0.00..116.50 rows=250 width=4)
516 Filter: (o.four = t.four)
519 This rather artificial example serves to illustrate a couple of
520 points: values from the outer plan level can be passed down into a
521 subplan (here, <code class="literal">t.four</code> is passed down) and the
522 results of the sub-select are available to the outer plan. Those
523 result values are shown by <code class="command">EXPLAIN</code> with notations
525 <code class="literal">(<em class="replaceable"><code>subplan_name</code></em>).col<em class="replaceable"><code>N</code></em></code>,
526 which refers to the <em class="replaceable"><code>N</code></em>'th output column of
527 the sub-<code class="literal">SELECT</code>.
529 <a id="id-1.5.13.4.7.32.1" class="indexterm"></a>
530 In the example above, the <code class="literal">ALL</code> operator runs the
531 subplan again for each row of the outer query (which accounts for the
532 high estimated cost). Some queries can use a <em class="firstterm">hashed
533 subplan</em> to avoid that:
535 </p><pre class="screen">
538 WHERE t.unique1 NOT IN (SELECT o.unique1 FROM onek o);
541 --------------------------------------------------------------------------------------------
542 Seq Scan on tenk1 t (cost=61.77..531.77 rows=5000 width=244)
543 Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1)))
545 -> Index Only Scan using onek_unique1 on onek o (cost=0.28..59.27 rows=1000 width=4)
549 Here, the subplan is run a single time and its output is loaded into
550 an in-memory hash table, which is then probed by the
551 outer <code class="literal">ANY</code> operator. This requires that the
552 sub-<code class="literal">SELECT</code> not reference any variables of the outer
553 query, and that the <code class="literal">ANY</code>'s comparison operator be
556 <a id="id-1.5.13.4.7.33.1" class="indexterm"></a>
557 If, in addition to not referencing any variables of the outer query,
558 the sub-<code class="literal">SELECT</code> cannot return more than one row,
559 it may instead be implemented as an <em class="firstterm">initplan</em>:
561 </p><pre class="screen">
562 EXPLAIN VERBOSE SELECT unique1
563 FROM tenk1 t1 WHERE t1.ten = (SELECT (random() * 10)::integer);
566 --------------------------------------------------------------------
567 Seq Scan on public.tenk1 t1 (cost=0.02..470.02 rows=1000 width=4)
569 Filter: (t1.ten = (InitPlan 1).col1)
571 -> Result (cost=0.00..0.02 rows=1 width=4)
572 Output: ((random() * '10'::double precision))::integer
575 An initplan is run only once per execution of the outer plan, and its
576 results are saved for re-use in later rows of the outer plan. So in
577 this example <code class="literal">random()</code> is evaluated only once and
578 all the values of <code class="literal">t1.ten</code> are compared to the same
579 randomly-chosen integer. That's quite different from what would
580 happen without the sub-<code class="literal">SELECT</code> construct.
581 </p></div><div class="sect2" id="USING-EXPLAIN-ANALYZE"><div class="titlepage"><div><div><h3 class="title">14.1.2. <code class="command">EXPLAIN ANALYZE</code> <a href="#USING-EXPLAIN-ANALYZE" class="id_link">#</a></h3></div></div></div><p>
582 It is possible to check the accuracy of the planner's estimates
583 by using <code class="command">EXPLAIN</code>'s <code class="literal">ANALYZE</code> option. With this
584 option, <code class="command">EXPLAIN</code> actually executes the query, and then displays
585 the true row counts and true run time accumulated within each plan node,
586 along with the same estimates that a plain <code class="command">EXPLAIN</code>
587 shows. For example, we might get a result like this:
589 </p><pre class="screen">
590 EXPLAIN ANALYZE SELECT *
591 FROM tenk1 t1, tenk2 t2
592 WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;
595 ---------------------------------------------------------------------------------------------------------------------------------
596 Nested Loop (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10.00 loops=1)
597 Buffers: shared hit=36 read=6
598 -> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244) (actual time=0.009..0.017 rows=10.00 loops=1)
599 Recheck Cond: (unique1 < 10)
600 Heap Blocks: exact=10
601 Buffers: shared hit=3 read=5 written=4
602 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10.00 loops=1)
603 Index Cond: (unique1 < 10)
605 Buffers: shared hit=2
606 -> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1.00 loops=10)
607 Index Cond: (unique2 = t1.unique2)
609 Buffers: shared hit=24 read=6
611 Buffers: shared hit=15 dirtied=9
612 Planning Time: 0.485 ms
613 Execution Time: 0.073 ms
616 Note that the <span class="quote">“<span class="quote">actual time</span>”</span> values are in milliseconds of
617 real time, whereas the <code class="literal">cost</code> estimates are expressed in
618 arbitrary units; so they are unlikely to match up.
619 The thing that's usually most important to look for is whether the
620 estimated row counts are reasonably close to reality. In this example
621 the estimates were all dead-on, but that's quite unusual in practice.
623 In some query plans, it is possible for a subplan node to be executed more
624 than once. For example, the inner index scan will be executed once per
625 outer row in the above nested-loop plan. In such cases, the
626 <code class="literal">loops</code> value reports the
627 total number of executions of the node, and the actual time and rows
628 values shown are averages per-execution. This is done to make the numbers
629 comparable with the way that the cost estimates are shown. Multiply by
630 the <code class="literal">loops</code> value to get the total time actually spent in
631 the node. In the above example, we spent a total of 0.030 milliseconds
632 executing the index scans on <code class="literal">tenk2</code>.
634 In some cases <code class="command">EXPLAIN ANALYZE</code> shows additional execution
635 statistics beyond the plan node execution times and row counts.
636 For example, Sort and Hash nodes provide extra information:
638 </p><pre class="screen">
639 EXPLAIN ANALYZE SELECT *
640 FROM tenk1 t1, tenk2 t2
641 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;
644 --------------------------------------------------------------------------------------------------------------------------------------------
645 Sort (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100.00 loops=1)
646 Sort Key: t1.fivethous
647 Sort Method: quicksort Memory: 74kB
648 Buffers: shared hit=440
649 -> Hash Join (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100.00 loops=1)
650 Hash Cond: (t2.unique2 = t1.unique2)
651 Buffers: shared hit=437
652 -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.026..1.790 rows=10000.00 loops=1)
653 Buffers: shared hit=345
654 -> Hash (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100.00 loops=1)
655 Buckets: 1024 Batches: 1 Memory Usage: 35kB
656 Buffers: shared hit=92
657 -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244) (actual time=0.030..0.450 rows=100.00 loops=1)
658 Recheck Cond: (unique1 < 100)
659 Heap Blocks: exact=90
660 Buffers: shared hit=92
661 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100.00 loops=1)
662 Index Cond: (unique1 < 100)
664 Buffers: shared hit=2
666 Buffers: shared hit=12
667 Planning Time: 0.187 ms
668 Execution Time: 3.036 ms
671 The Sort node shows the sort method used (in particular, whether the sort
672 was in-memory or on-disk) and the amount of memory or disk space needed.
673 The Hash node shows the number of hash buckets and batches as well as the
674 peak amount of memory used for the hash table. (If the number of batches
675 exceeds one, there will also be disk space usage involved, but that is not
678 Index Scan nodes (as well as Bitmap Index Scan and Index-Only Scan nodes)
679 show an <span class="quote">“<span class="quote">Index Searches</span>”</span> line that reports the total number
680 of searches across <span class="emphasis"><em>all</em></span> node
681 executions/<code class="literal">loops</code>:
683 </p><pre class="screen">
684 EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE thousand IN (1, 500, 700, 999);
686 ----------------------------------------------------------------------------------------------------------------------------------
687 Bitmap Heap Scan on tenk1 (cost=9.45..73.44 rows=40 width=244) (actual time=0.012..0.028 rows=40.00 loops=1)
688 Recheck Cond: (thousand = ANY ('{1,500,700,999}'::integer[]))
689 Heap Blocks: exact=39
690 Buffers: shared hit=47
691 -> Bitmap Index Scan on tenk1_thous_tenthous (cost=0.00..9.44 rows=40 width=0) (actual time=0.009..0.009 rows=40.00 loops=1)
692 Index Cond: (thousand = ANY ('{1,500,700,999}'::integer[]))
694 Buffers: shared hit=8
695 Planning Time: 0.029 ms
696 Execution Time: 0.034 ms
699 Here we see a Bitmap Index Scan node that needed 4 separate index
700 searches. The scan had to search the index from the
701 <code class="structname">tenk1_thous_tenthous</code> index root page once per
702 <code class="type">integer</code> value from the predicate's <code class="literal">IN</code>
703 construct. However, the number of index searches often won't have such a
704 simple correspondence to the query predicate:
706 </p><pre class="screen">
707 EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE thousand IN (1, 2, 3, 4);
709 ----------------------------------------------------------------------------------------------------------------------------------
710 Bitmap Heap Scan on tenk1 (cost=9.45..73.44 rows=40 width=244) (actual time=0.009..0.019 rows=40.00 loops=1)
711 Recheck Cond: (thousand = ANY ('{1,2,3,4}'::integer[]))
712 Heap Blocks: exact=38
713 Buffers: shared hit=40
714 -> Bitmap Index Scan on tenk1_thous_tenthous (cost=0.00..9.44 rows=40 width=0) (actual time=0.005..0.005 rows=40.00 loops=1)
715 Index Cond: (thousand = ANY ('{1,2,3,4}'::integer[]))
717 Buffers: shared hit=2
718 Planning Time: 0.029 ms
719 Execution Time: 0.026 ms
722 This variant of our <code class="literal">IN</code> query performed only 1 index
723 search. It spent less time traversing the index (compared to the original
724 query) because its <code class="literal">IN</code> construct uses values matching
725 index tuples stored next to each other, on the same
726 <code class="structname">tenk1_thous_tenthous</code> index leaf page.
728 The <span class="quote">“<span class="quote">Index Searches</span>”</span> line is also useful with B-tree index
729 scans that apply the <em class="firstterm">skip scan</em> optimization to
730 more efficiently traverse through an index:
731 </p><pre class="screen">
732 EXPLAIN ANALYZE SELECT four, unique1 FROM tenk1 WHERE four BETWEEN 1 AND 3 AND unique1 = 42;
734 ----------------------------------------------------------------------------------------------------------------------------------
735 Index Only Scan using tenk1_four_unique1_idx on tenk1 (cost=0.29..6.90 rows=1 width=8) (actual time=0.006..0.007 rows=1.00 loops=1)
736 Index Cond: ((four >= 1) AND (four <= 3) AND (unique1 = 42))
739 Buffers: shared hit=7
740 Planning Time: 0.029 ms
741 Execution Time: 0.012 ms
744 Here we see an Index-Only Scan node using
745 <code class="structname">tenk1_four_unique1_idx</code>, a multi-column index on the
746 <code class="structname">tenk1</code> table's <code class="structfield">four</code> and
747 <code class="structfield">unique1</code> columns. The scan performs 3 searches
748 that each read a single index leaf page:
749 <span class="quote">“<span class="quote"><code class="literal">four = 1 AND unique1 = 42</code></span>”</span>,
750 <span class="quote">“<span class="quote"><code class="literal">four = 2 AND unique1 = 42</code></span>”</span>, and
751 <span class="quote">“<span class="quote"><code class="literal">four = 3 AND unique1 = 42</code></span>”</span>. This index
752 is generally a good target for skip scan, since, as discussed in
753 <a class="xref" href="indexes-multicolumn.html" title="11.3. Multicolumn Indexes">Section 11.3</a>, its leading column (the
754 <code class="structfield">four</code> column) contains only 4 distinct values,
755 while its second/final column (the <code class="structfield">unique1</code>
756 column) contains many distinct values.
758 Another type of extra information is the number of rows removed by a
761 </p><pre class="screen">
762 EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;
765 ---------------------------------------------------------------------------------------------------------
766 Seq Scan on tenk1 (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000.00 loops=1)
768 Rows Removed by Filter: 3000
769 Buffers: shared hit=345
770 Planning Time: 0.102 ms
771 Execution Time: 2.145 ms
774 These counts can be particularly valuable for filter conditions applied at
775 join nodes. The <span class="quote">“<span class="quote">Rows Removed</span>”</span> line only appears when at least
776 one scanned row, or potential join pair in the case of a join node,
777 is rejected by the filter condition.
779 A case similar to filter conditions occurs with <span class="quote">“<span class="quote">lossy</span>”</span>
780 index scans. For example, consider this search for polygons containing a
783 </p><pre class="screen">
784 EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';
787 ------------------------------------------------------------------------------------------------------
788 Seq Scan on polygon_tbl (cost=0.00..1.09 rows=1 width=85) (actual time=0.023..0.023 rows=0.00 loops=1)
789 Filter: (f1 @> '((0.5,2))'::polygon)
790 Rows Removed by Filter: 7
791 Buffers: shared hit=1
792 Planning Time: 0.039 ms
793 Execution Time: 0.033 ms
796 The planner thinks (quite correctly) that this sample table is too small
797 to bother with an index scan, so we have a plain sequential scan in which
798 all the rows got rejected by the filter condition. But if we force an
799 index scan to be used, we see:
801 </p><pre class="screen">
802 SET enable_seqscan TO off;
804 EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';
807 --------------------------------------------------------------------------------------------------------------------------
808 Index Scan using gpolygonind on polygon_tbl (cost=0.13..8.15 rows=1 width=85) (actual time=0.074..0.074 rows=0.00 loops=1)
809 Index Cond: (f1 @> '((0.5,2))'::polygon)
810 Rows Removed by Index Recheck: 1
812 Buffers: shared hit=1
813 Planning Time: 0.039 ms
814 Execution Time: 0.098 ms
817 Here we can see that the index returned one candidate row, which was
818 then rejected by a recheck of the index condition. This happens because a
819 GiST index is <span class="quote">“<span class="quote">lossy</span>”</span> for polygon containment tests: it actually
820 returns the rows with polygons that overlap the target, and then we have
821 to do the exact containment test on those rows.
823 <code class="command">EXPLAIN</code> has a <code class="literal">BUFFERS</code> option which
824 provides additional detail about I/O operations performed during the
825 planning and execution of the given query. The buffer numbers displayed
826 show the count of the non-distinct buffers hit, read, dirtied, and written
827 for the given node and all of its child nodes. The
828 <code class="literal">ANALYZE</code> option implicitly enables the
829 <code class="literal">BUFFERS</code> option. If this
830 is undesired, <code class="literal">BUFFERS</code> may be explicitly disabled:
832 </p><pre class="screen">
833 EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
836 ---------------------------------------------------------------------------------------------------------------------------------
837 Bitmap Heap Scan on tenk1 (cost=25.07..60.11 rows=10 width=244) (actual time=0.105..0.114 rows=10.00 loops=1)
838 Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
839 Heap Blocks: exact=10
840 -> BitmapAnd (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0.00 loops=1)
841 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.027..0.027 rows=100.00 loops=1)
842 Index Cond: (unique1 < 100)
844 -> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) (actual time=0.070..0.070 rows=999.00 loops=1)
845 Index Cond: (unique2 > 9000)
847 Planning Time: 0.162 ms
848 Execution Time: 0.143 ms
851 Keep in mind that because <code class="command">EXPLAIN ANALYZE</code> actually
852 runs the query, any side-effects will happen as usual, even though
853 whatever results the query might output are discarded in favor of
854 printing the <code class="command">EXPLAIN</code> data. If you want to analyze a
855 data-modifying query without changing your tables, you can
856 roll the command back afterwards, for example:
858 </p><pre class="screen">
861 EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;
864 --------------------------------------------------------------------------------------------------------------------------------
865 Update on tenk1 (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0.00 loops=1)
866 -> Bitmap Heap Scan on tenk1 (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100.00 loops=1)
867 Recheck Cond: (unique1 < 100)
868 Heap Blocks: exact=90
869 Buffers: shared hit=4 read=2
870 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100.00 loops=1)
871 Index Cond: (unique1 < 100)
873 Buffers: shared read=2
874 Planning Time: 0.151 ms
875 Execution Time: 1.856 ms
880 As seen in this example, when the query is an <code class="command">INSERT</code>,
881 <code class="command">UPDATE</code>, <code class="command">DELETE</code>, or
882 <code class="command">MERGE</code> command, the actual work of
883 applying the table changes is done by a top-level Insert, Update,
884 Delete, or Merge plan node. The plan nodes underneath this node perform
885 the work of locating the old rows and/or computing the new data.
886 So above, we see the same sort of bitmap table scan we've seen already,
887 and its output is fed to an Update node that stores the updated rows.
888 It's worth noting that although the data-modifying node can take a
889 considerable amount of run time (here, it's consuming the lion's share
890 of the time), the planner does not currently add anything to the cost
891 estimates to account for that work. That's because the work to be done is
892 the same for every correct query plan, so it doesn't affect planning
895 When an <code class="command">UPDATE</code>, <code class="command">DELETE</code>, or
896 <code class="command">MERGE</code> command affects a partitioned table or
897 inheritance hierarchy, the output might look like this:
899 </p><pre class="screen">
900 EXPLAIN UPDATE gtest_parent SET f1 = CURRENT_DATE WHERE f2 = 101;
903 ----------------------------------------------------------------------------------------
904 Update on gtest_parent (cost=0.00..3.06 rows=0 width=0)
905 Update on gtest_child gtest_parent_1
906 Update on gtest_child2 gtest_parent_2
907 Update on gtest_child3 gtest_parent_3
908 -> Append (cost=0.00..3.06 rows=3 width=14)
909 -> Seq Scan on gtest_child gtest_parent_1 (cost=0.00..1.01 rows=1 width=14)
911 -> Seq Scan on gtest_child2 gtest_parent_2 (cost=0.00..1.01 rows=1 width=14)
913 -> Seq Scan on gtest_child3 gtest_parent_3 (cost=0.00..1.01 rows=1 width=14)
917 In this example the Update node needs to consider three child tables,
918 but not the originally-mentioned partitioned table (since that never
919 stores any data). So there are three input
920 scanning subplans, one per table. For clarity, the Update node is
921 annotated to show the specific target tables that will be updated, in the
922 same order as the corresponding subplans.
924 The <code class="literal">Planning time</code> shown by <code class="command">EXPLAIN
925 ANALYZE</code> is the time it took to generate the query plan from the
926 parsed query and optimize it. It does not include parsing or rewriting.
928 The <code class="literal">Execution time</code> shown by <code class="command">EXPLAIN
929 ANALYZE</code> includes executor start-up and shut-down time, as well
930 as the time to run any triggers that are fired, but it does not include
931 parsing, rewriting, or planning time.
932 Time spent executing <code class="literal">BEFORE</code> triggers, if any, is included in
933 the time for the related Insert, Update, or Delete node; but time
934 spent executing <code class="literal">AFTER</code> triggers is not counted there because
935 <code class="literal">AFTER</code> triggers are fired after completion of the whole plan.
936 The total time spent in each trigger
937 (either <code class="literal">BEFORE</code> or <code class="literal">AFTER</code>) is also shown separately.
938 Note that deferred constraint triggers will not be executed
939 until end of transaction and are thus not considered at all by
940 <code class="command">EXPLAIN ANALYZE</code>.
942 The time shown for the top-level node does not include any time needed
943 to convert the query's output data into displayable form or to send it
944 to the client. While <code class="command">EXPLAIN ANALYZE</code> will never
945 send the data to the client, it can be told to convert the query's
946 output data to displayable form and measure the time needed for that,
947 by specifying the <code class="literal">SERIALIZE</code> option. That time will
948 be shown separately, and it's also included in the
949 total <code class="literal">Execution time</code>.
950 </p></div><div class="sect2" id="USING-EXPLAIN-CAVEATS"><div class="titlepage"><div><div><h3 class="title">14.1.3. Caveats <a href="#USING-EXPLAIN-CAVEATS" class="id_link">#</a></h3></div></div></div><p>
951 There are two significant ways in which run times measured by
952 <code class="command">EXPLAIN ANALYZE</code> can deviate from normal execution of
953 the same query. First, since no output rows are delivered to the client,
954 network transmission costs are not included. I/O conversion costs are
955 not included either unless <code class="literal">SERIALIZE</code> is specified.
956 Second, the measurement overhead added by <code class="command">EXPLAIN
957 ANALYZE</code> can be significant, especially on machines with slow
958 <code class="function">gettimeofday()</code> operating-system calls. You can use the
959 <a class="xref" href="pgtesttiming.html" title="pg_test_timing"><span class="refentrytitle"><span class="application">pg_test_timing</span></span></a> tool to measure the overhead of timing
962 <code class="command">EXPLAIN</code> results should not be extrapolated to situations
963 much different from the one you are actually testing; for example,
964 results on a toy-sized table cannot be assumed to apply to large tables.
965 The planner's cost estimates are not linear and so it might choose
966 a different plan for a larger or smaller table. An extreme example
967 is that on a table that only occupies one disk page, you'll nearly
968 always get a sequential scan plan whether indexes are available or not.
969 The planner realizes that it's going to take one disk page read to
970 process the table in any case, so there's no value in expending additional
971 page reads to look at an index. (We saw this happening in the
972 <code class="literal">polygon_tbl</code> example above.)
974 There are cases in which the actual and estimated values won't match up
975 well, but nothing is really wrong. One such case occurs when
976 plan node execution is stopped short by a <code class="literal">LIMIT</code> or similar
977 effect. For example, in the <code class="literal">LIMIT</code> query we used before,
979 </p><pre class="screen">
980 EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;
983 -------------------------------------------------------------------------------------------------------------------------------
984 Limit (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2.00 loops=1)
985 Buffers: shared hit=16
986 -> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..70.50 rows=10 width=244) (actual time=0.051..0.070 rows=2.00 loops=1)
987 Index Cond: (unique2 > 9000)
988 Filter: (unique1 < 100)
989 Rows Removed by Filter: 287
991 Buffers: shared hit=16
992 Planning Time: 0.077 ms
993 Execution Time: 0.086 ms
996 the estimated cost and row count for the Index Scan node are shown as
997 though it were run to completion. But in reality the Limit node stopped
998 requesting rows after it got two, so the actual row count is only 2 and
999 the run time is less than the cost estimate would suggest. This is not
1000 an estimation error, only a discrepancy in the way the estimates and true
1001 values are displayed.
1003 Merge joins also have measurement artifacts that can confuse the unwary.
1004 A merge join will stop reading one input if it's exhausted the other input
1005 and the next key value in the one input is greater than the last key value
1006 of the other input; in such a case there can be no more matches and so no
1007 need to scan the rest of the first input. This results in not reading all
1008 of one child, with results like those mentioned for <code class="literal">LIMIT</code>.
1009 Also, if the outer (first) child contains rows with duplicate key values,
1010 the inner (second) child is backed up and rescanned for the portion of its
1011 rows matching that key value. <code class="command">EXPLAIN ANALYZE</code> counts these
1012 repeated emissions of the same inner rows as if they were real additional
1013 rows. When there are many outer duplicates, the reported actual row count
1014 for the inner child plan node can be significantly larger than the number
1015 of rows that are actually in the inner relation.
1017 BitmapAnd and BitmapOr nodes always report their actual row counts as zero,
1018 due to implementation limitations.
1020 Normally, <code class="command">EXPLAIN</code> will display every plan node
1021 created by the planner. However, there are cases where the executor
1022 can determine that certain nodes need not be executed because they
1023 cannot produce any rows, based on parameter values that were not
1024 available at planning time. (Currently this can only happen for child
1025 nodes of an Append or MergeAppend node that is scanning a partitioned
1026 table.) When this happens, those plan nodes are omitted from
1027 the <code class="command">EXPLAIN</code> output and a <code class="literal">Subplans
1028 Removed: <em class="replaceable"><code>N</code></em></code> annotation appears
1030 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="performance-tips.html" title="Chapter 14. Performance Tips">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="performance-tips.html" title="Chapter 14. Performance Tips">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="planner-stats.html" title="14.2. Statistics Used by the Planner">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 14. Performance Tips </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 14.2. Statistics Used by the Planner</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob