Debian Codegraph @

doc/src/sgml/perform.sgml

raw
1 <!-- doc/src/sgml/perform.sgml -->
2
3 <chapter id="performance-tips">
4 <title>Performance Tips</title>
5
6 <indexterm zone="performance-tips">
7 <primary>performance</primary>
8 </indexterm>
9
10 <para>
11 Query performance can be affected by many things. Some of these can
12 be controlled by the user, while others are fundamental to the underlying
13 design of the system. This chapter provides some hints about understanding
14 and tuning <productname>PostgreSQL</productname> performance.
15 </para>
16
17 <sect1 id="using-explain">
18 <title>Using <command>EXPLAIN</command></title>
19
20 <indexterm zone="using-explain">
21 <primary>EXPLAIN</primary>
22 </indexterm>
23
24 <indexterm zone="using-explain">
25 <primary>query plan</primary>
26 </indexterm>
27
28 <para>
29 <productname>PostgreSQL</productname> devises a <firstterm>query
30 plan</firstterm> for each query it receives. Choosing the right
31 plan to match the query structure and the properties of the data
32 is absolutely critical for good performance, so the system includes
33 a complex <firstterm>planner</firstterm> that tries to choose good plans.
34 You can use the <link linkend="sql-explain"><command>EXPLAIN</command></link> command
35 to see what query plan the planner creates for any query.
36 Plan-reading is an art that requires some experience to master,
37 but this section attempts to cover the basics.
38 </para>
39
40 <para>
41 Examples in this section are drawn from the regression test database
42 after doing a <command>VACUUM ANALYZE</command>, using v18 development sources.
43 You should be able to get similar results if you try the examples
44 yourself, but your estimated costs and row counts might vary slightly
45 because <command>ANALYZE</command>'s statistics are random samples rather
46 than exact, and because costs are inherently somewhat platform-dependent.
47 </para>
48
49 <para>
50 The examples use <command>EXPLAIN</command>'s default <quote>text</quote> output
51 format, which is compact and convenient for humans to read.
52 If you want to feed <command>EXPLAIN</command>'s output to a program for further
53 analysis, you should use one of its machine-readable output formats
54 (XML, JSON, or YAML) instead.
55 </para>
56
57 <sect2 id="using-explain-basics">
58 <title><command>EXPLAIN</command> Basics</title>
59
60 <para>
61 The structure of a query plan is a tree of <firstterm>plan nodes</firstterm>.
62 Nodes at the bottom level of the tree are scan nodes: they return raw rows
63 from a table. There are different types of scan nodes for different
64 table access methods: sequential scans, index scans, and bitmap index
65 scans. There are also non-table row sources, such as <literal>VALUES</literal>
66 clauses and set-returning functions in <literal>FROM</literal>, which have their
67 own scan node types.
68 If the query requires joining, aggregation, sorting, or other
69 operations on the raw rows, then there will be additional nodes
70 above the scan nodes to perform these operations. Again,
71 there is usually more than one possible way to do these operations,
72 so different node types can appear here too. The output
73 of <command>EXPLAIN</command> has one line for each node in the plan
74 tree, showing the basic node type plus the cost estimates that the planner
75 made for the execution of that plan node. Additional lines might appear,
76 indented from the node's summary line,
77 to show additional properties of the node.
78 The very first line (the summary line for the topmost
79 node) has the estimated total execution cost for the plan; it is this
80 number that the planner seeks to minimize.
81 </para>
82
83 <para>
84 Here is a trivial example, just to show what the output looks like:
85
86 <screen>
87 EXPLAIN SELECT * FROM tenk1;
88
89 QUERY PLAN
90 -------------------------------------------------------------
91 Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
92 </screen>
93 </para>
94
95 <para>
96 Since this query has no <literal>WHERE</literal> clause, it must scan all the
97 rows of the table, so the planner has chosen to use a simple sequential
98 scan plan. The numbers that are quoted in parentheses are (left
99 to right):
100
101 <itemizedlist>
102 <listitem>
103 <para>
104 Estimated start-up cost. This is the time expended before the output
105 phase can begin, e.g., time to do the sorting in a sort node.
106 </para>
107 </listitem>
108
109 <listitem>
110 <para>
111 Estimated total cost. This is stated on the assumption that the plan
112 node is run to completion, i.e., all available rows are retrieved.
113 In practice a node's parent node might stop short of reading all
114 available rows (see the <literal>LIMIT</literal> example below).
115 </para>
116 </listitem>
117
118 <listitem>
119 <para>
120 Estimated number of rows output by this plan node. Again, the node
121 is assumed to be run to completion.
122 </para>
123 </listitem>
124
125 <listitem>
126 <para>
127 Estimated average width of rows output by this plan node (in bytes).
128 </para>
129 </listitem>
130 </itemizedlist>
131 </para>
132
133 <para>
134 The costs are measured in arbitrary units determined by the planner's
135 cost parameters (see <xref linkend="runtime-config-query-constants"/>).
136 Traditional practice is to measure the costs in units of disk page
137 fetches; that is, <xref linkend="guc-seq-page-cost"/> is conventionally
138 set to <literal>1.0</literal> and the other cost parameters are set relative
139 to that. The examples in this section are run with the default cost
140 parameters.
141 </para>
142
143 <para>
144 It's important to understand that the cost of an upper-level node includes
145 the cost of all its child nodes. It's also important to realize that
146 the cost only reflects things that the planner cares about.
147 In particular, the cost does not consider the time spent to convert
148 output values to text form or to transmit them to the client, which
149 could be important factors in the real elapsed time; but the planner
150 ignores those costs because it cannot change them by altering the
151 plan. (Every correct plan will output the same row set, we trust.)
152 </para>
153
154 <para>
155 The <literal>rows</literal> value is a little tricky because it is
156 not the number of rows processed or scanned by the
157 plan node, but rather the number emitted by the node. This is often
158 less than the number scanned, as a result of filtering by any
159 <literal>WHERE</literal>-clause conditions that are being applied at the node.
160 Ideally the top-level rows estimate will approximate the number of rows
161 actually returned, updated, or deleted by the query.
162 </para>
163
164 <para>
165 Returning to our example:
166
167 <screen>
168 EXPLAIN SELECT * FROM tenk1;
169
170 QUERY PLAN
171 -------------------------------------------------------------
172 Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
173 </screen>
174 </para>
175
176 <para>
177 These numbers are derived very straightforwardly. If you do:
178
179 <programlisting>
180 SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
181 </programlisting>
182
183 you will find that <classname>tenk1</classname> has 345 disk
184 pages and 10000 rows. The estimated cost is computed as (disk pages read *
185 <xref linkend="guc-seq-page-cost"/>) + (rows scanned *
186 <xref linkend="guc-cpu-tuple-cost"/>). By default,
187 <varname>seq_page_cost</varname> is 1.0 and <varname>cpu_tuple_cost</varname> is 0.01,
188 so the estimated cost is (345 * 1.0) + (10000 * 0.01) = 445.
189 </para>
190
191 <para>
192 Now let's modify the query to add a <literal>WHERE</literal> condition:
193
194 <screen>
195 EXPLAIN SELECT * FROM tenk1 WHERE unique1 &lt; 7000;
196
197 QUERY PLAN
198 ------------------------------------------------------------
199 Seq Scan on tenk1 (cost=0.00..470.00 rows=7000 width=244)
200 Filter: (unique1 &lt; 7000)
201 </screen>
202
203 Notice that the <command>EXPLAIN</command> output shows the <literal>WHERE</literal>
204 clause being applied as a <quote>filter</quote> condition attached to the Seq
205 Scan plan node. This means that
206 the plan node checks the condition for each row it scans, and outputs
207 only the ones that pass the condition.
208 The estimate of output rows has been reduced because of the
209 <literal>WHERE</literal> clause.
210 However, the scan will still have to visit all 10000 rows, so the cost
211 hasn't decreased; in fact it has gone up a bit (by 10000 * <xref
212 linkend="guc-cpu-operator-cost"/>, to be exact) to reflect the extra CPU
213 time spent checking the <literal>WHERE</literal> condition.
214 </para>
215
216 <para>
217 The actual number of rows this query would select is 7000, but the <literal>rows</literal>
218 estimate is only approximate. If you try to duplicate this experiment,
219 you may well get a slightly different estimate; moreover, it can
220 change after each <command>ANALYZE</command> command, because the
221 statistics produced by <command>ANALYZE</command> are taken from a
222 randomized sample of the table.
223 </para>
224
225 <para>
226 Now, let's make the condition more restrictive:
227
228 <screen>
229 EXPLAIN SELECT * FROM tenk1 WHERE unique1 &lt; 100;
230
231 QUERY PLAN
232 -------------------------------------------------------------------&zwsp;-----------
233 Bitmap Heap Scan on tenk1 (cost=5.06..224.98 rows=100 width=244)
234 Recheck Cond: (unique1 &lt; 100)
235 -&gt; Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
236 Index Cond: (unique1 &lt; 100)
237 </screen>
238
239 Here the planner has decided to use a two-step plan: the child plan
240 node visits an index to find the locations of rows matching the index
241 condition, and then the upper plan node actually fetches those rows
242 from the table itself. Fetching rows separately is much more
243 expensive than reading them sequentially, but because not all the pages
244 of the table have to be visited, this is still cheaper than a sequential
245 scan. (The reason for using two plan levels is that the upper plan
246 node sorts the row locations identified by the index into physical order
247 before reading them, to minimize the cost of separate fetches.
248 The <quote>bitmap</quote> mentioned in the node names is the mechanism that
249 does the sorting.)
250 </para>
251
252 <para>
253 Now let's add another condition to the <literal>WHERE</literal> clause:
254
255 <screen>
256 EXPLAIN SELECT * FROM tenk1 WHERE unique1 &lt; 100 AND stringu1 = 'xxx';
257
258 QUERY PLAN
259 -------------------------------------------------------------------&zwsp;-----------
260 Bitmap Heap Scan on tenk1 (cost=5.04..225.20 rows=1 width=244)
261 Recheck Cond: (unique1 &lt; 100)
262 Filter: (stringu1 = 'xxx'::name)
263 -&gt; Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
264 Index Cond: (unique1 &lt; 100)
265 </screen>
266
267 The added condition <literal>stringu1 = 'xxx'</literal> reduces the
268 output row count estimate, but not the cost because we still have to visit
269 the same set of rows. That's because the <literal>stringu1</literal> clause
270 cannot be applied as an index condition, since this index is only on
271 the <literal>unique1</literal> column. Instead it is applied as a filter on
272 the rows retrieved using the index. Thus the cost has actually gone up
273 slightly to reflect this extra checking.
274 </para>
275
276 <para>
277 In some cases the planner will prefer a <quote>simple</quote> index scan plan:
278
279 <screen>
280 EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;
281
282 QUERY PLAN
283 -------------------------------------------------------------------&zwsp;----------
284 Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244)
285 Index Cond: (unique1 = 42)
286 </screen>
287
288 In this type of plan the table rows are fetched in index order, which
289 makes them even more expensive to read, but there are so few that the
290 extra cost of sorting the row locations is not worth it. You'll most
291 often see this plan type for queries that fetch just a single row. It's
292 also often used for queries that have an <literal>ORDER BY</literal> condition
293 that matches the index order, because then no extra sorting step is needed
294 to satisfy the <literal>ORDER BY</literal>. In this example, adding
295 <literal>ORDER BY unique1</literal> would use the same plan because the
296 index already implicitly provides the requested ordering.
297 </para>
298
299 <para>
300 The planner may implement an <literal>ORDER BY</literal> clause in several
301 ways. The above example shows that such an ordering clause may be
302 implemented implicitly. The planner may also add an explicit
303 <literal>Sort</literal> step:
304
305 <screen>
306 EXPLAIN SELECT * FROM tenk1 ORDER BY unique1;
307
308 QUERY PLAN
309 -------------------------------------------------------------------
310 Sort (cost=1109.39..1134.39 rows=10000 width=244)
311 Sort Key: unique1
312 -> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
313 </screen>
314
315 If a part of the plan guarantees an ordering on a prefix of the
316 required sort keys, then the planner may instead decide to use an
317 <literal>Incremental Sort</literal> step:
318
319 <screen>
320 EXPLAIN SELECT * FROM tenk1 ORDER BY hundred, ten LIMIT 100;
321
322 QUERY PLAN
323 -------------------------------------------------------------------&zwsp;-----------------------------
324 Limit (cost=19.35..39.49 rows=100 width=244)
325 -&gt; Incremental Sort (cost=19.35..2033.39 rows=10000 width=244)
326 Sort Key: hundred, ten
327 Presorted Key: hundred
328 -&gt; Index Scan using tenk1_hundred on tenk1 (cost=0.29..1574.20 rows=10000 width=244)
329 </screen>
330
331 Compared to regular sorts, sorting incrementally allows returning tuples
332 before the entire result set has been sorted, which particularly enables
333 optimizations with <literal>LIMIT</literal> queries. It may also reduce
334 memory usage and the likelihood of spilling sorts to disk, but it comes at
335 the cost of the increased overhead of splitting the result set into multiple
336 sorting batches.
337 </para>
338
339 <para>
340 If there are separate indexes on several of the columns referenced
341 in <literal>WHERE</literal>, the planner might choose to use an AND or OR
342 combination of the indexes:
343
344 <screen>
345 EXPLAIN SELECT * FROM tenk1 WHERE unique1 &lt; 100 AND unique2 &gt; 9000;
346
347 QUERY PLAN
348 -------------------------------------------------------------------&zwsp;------------------
349 Bitmap Heap Scan on tenk1 (cost=25.07..60.11 rows=10 width=244)
350 Recheck Cond: ((unique1 &lt; 100) AND (unique2 &gt; 9000))
351 -&gt; BitmapAnd (cost=25.07..25.07 rows=10 width=0)
352 -&gt; Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
353 Index Cond: (unique1 &lt; 100)
354 -&gt; Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0)
355 Index Cond: (unique2 &gt; 9000)
356 </screen>
357
358 But this requires visiting both indexes, so it's not necessarily a win
359 compared to using just one index and treating the other condition as
360 a filter. If you vary the ranges involved you'll see the plan change
361 accordingly.
362 </para>
363
364 <para>
365 Here is an example showing the effects of <literal>LIMIT</literal>:
366
367 <screen>
368 EXPLAIN SELECT * FROM tenk1 WHERE unique1 &lt; 100 AND unique2 &gt; 9000 LIMIT 2;
369
370 QUERY PLAN
371 -------------------------------------------------------------------&zwsp;------------------
372 Limit (cost=0.29..14.28 rows=2 width=244)
373 -&gt; Index Scan using tenk1_unique2 on tenk1 (cost=0.29..70.27 rows=10 width=244)
374 Index Cond: (unique2 &gt; 9000)
375 Filter: (unique1 &lt; 100)
376 </screen>
377 </para>
378
379 <para>
380 This is the same query as above, but we added a <literal>LIMIT</literal> so that
381 not all the rows need be retrieved, and the planner changed its mind about
382 what to do. Notice that the total cost and row count of the Index Scan
383 node are shown as if it were run to completion. However, the Limit node
384 is expected to stop after retrieving only a fifth of those rows, so its
385 total cost is only a fifth as much, and that's the actual estimated cost
386 of the query. This plan is preferred over adding a Limit node to the
387 previous plan because the Limit could not avoid paying the startup cost
388 of the bitmap scan, so the total cost would be something over 25 units
389 with that approach.
390 </para>
391
392 <para>
393 Let's try joining two tables, using the columns we have been discussing:
394
395 <screen>
396 EXPLAIN SELECT *
397 FROM tenk1 t1, tenk2 t2
398 WHERE t1.unique1 &lt; 10 AND t1.unique2 = t2.unique2;
399
400 QUERY PLAN
401 -------------------------------------------------------------------&zwsp;-------------------
402 Nested Loop (cost=4.65..118.50 rows=10 width=488)
403 -&gt; Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244)
404 Recheck Cond: (unique1 &lt; 10)
405 -&gt; Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)
406 Index Cond: (unique1 &lt; 10)
407 -&gt; Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.90 rows=1 width=244)
408 Index Cond: (unique2 = t1.unique2)
409 </screen>
410 </para>
411
412 <para>
413 In this plan, we have a nested-loop join node with two table scans as
414 inputs, or children. The indentation of the node summary lines reflects
415 the plan tree structure. The join's first, or <quote>outer</quote>, child
416 is a bitmap scan similar to those we saw before. Its cost and row count
417 are the same as we'd get from <literal>SELECT ... WHERE unique1 &lt; 10</literal>
418 because we are
419 applying the <literal>WHERE</literal> clause <literal>unique1 &lt; 10</literal>
420 at that node.
421 The <literal>t1.unique2 = t2.unique2</literal> clause is not relevant yet,
422 so it doesn't affect the row count of the outer scan. The nested-loop
423 join node will run its second,
424 or <quote>inner</quote> child once for each row obtained from the outer child.
425 Column values from the current outer row can be plugged into the inner
426 scan; here, the <literal>t1.unique2</literal> value from the outer row is available,
427 so we get a plan and costs similar to what we saw above for a simple
428 <literal>SELECT ... WHERE t2.unique2 = <replaceable>constant</replaceable></literal> case.
429 (The estimated cost is actually a bit lower than what was seen above,
430 as a result of caching that's expected to occur during the repeated
431 index scans on <literal>t2</literal>.) The
432 costs of the loop node are then set on the basis of the cost of the outer
433 scan, plus one repetition of the inner scan for each outer row (10 * 7.90,
434 here), plus a little CPU time for join processing.
435 </para>
436
437 <para>
438 In this example the join's output row count is the same as the product
439 of the two scans' row counts, but that's not true in all cases because
440 there can be additional <literal>WHERE</literal> clauses that mention both tables
441 and so can only be applied at the join point, not to either input scan.
442 Here's an example:
443
444 <screen>
445 EXPLAIN SELECT *
446 FROM tenk1 t1, tenk2 t2
447 WHERE t1.unique1 &lt; 10 AND t2.unique2 &lt; 10 AND t1.hundred &lt; t2.hundred;
448
449 QUERY PLAN
450 -------------------------------------------------------------------&zwsp;--------------------------
451 Nested Loop (cost=4.65..49.36 rows=33 width=488)
452 Join Filter: (t1.hundred &lt; t2.hundred)
453 -&gt; Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244)
454 Recheck Cond: (unique1 &lt; 10)
455 -&gt; Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)
456 Index Cond: (unique1 &lt; 10)
457 -&gt; Materialize (cost=0.29..8.51 rows=10 width=244)
458 -&gt; Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..8.46 rows=10 width=244)
459 Index Cond: (unique2 &lt; 10)
460 </screen>
461
462 The condition <literal>t1.hundred &lt; t2.hundred</literal> can't be
463 tested in the <literal>tenk2_unique2</literal> index, so it's applied at the
464 join node. This reduces the estimated output row count of the join node,
465 but does not change either input scan.
466 </para>
467
468 <para>
469 Notice that here the planner has chosen to <quote>materialize</quote> the inner
470 relation of the join, by putting a Materialize plan node atop it. This
471 means that the <literal>t2</literal> index scan will be done just once, even
472 though the nested-loop join node needs to read that data ten times, once
473 for each row from the outer relation. The Materialize node saves the data
474 in memory as it's read, and then returns the data from memory on each
475 subsequent pass.
476 </para>
477
478 <para>
479 When dealing with outer joins, you might see join plan nodes with both
480 <quote>Join Filter</quote> and plain <quote>Filter</quote> conditions attached.
481 Join Filter conditions come from the outer join's <literal>ON</literal> clause,
482 so a row that fails the Join Filter condition could still get emitted as
483 a null-extended row. But a plain Filter condition is applied after the
484 outer-join rules and so acts to remove rows unconditionally. In an inner
485 join there is no semantic difference between these types of filters.
486 </para>
487
488 <para>
489 If we change the query's selectivity a bit, we might get a very different
490 join plan:
491
492 <screen>
493 EXPLAIN SELECT *
494 FROM tenk1 t1, tenk2 t2
495 WHERE t1.unique1 &lt; 100 AND t1.unique2 = t2.unique2;
496
497 QUERY PLAN
498 -------------------------------------------------------------------&zwsp;-----------------------
499 Hash Join (cost=226.23..709.73 rows=100 width=488)
500 Hash Cond: (t2.unique2 = t1.unique2)
501 -&gt; Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)
502 -&gt; Hash (cost=224.98..224.98 rows=100 width=244)
503 -&gt; Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)
504 Recheck Cond: (unique1 &lt; 100)
505 -&gt; Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
506 Index Cond: (unique1 &lt; 100)
507 </screen>
508 </para>
509
510 <para>
511 Here, the planner has chosen to use a hash join, in which rows of one
512 table are entered into an in-memory hash table, after which the other
513 table is scanned and the hash table is probed for matches to each row.
514 Again note how the indentation reflects the plan structure: the bitmap
515 scan on <literal>tenk1</literal> is the input to the Hash node, which constructs
516 the hash table. That's then returned to the Hash Join node, which reads
517 rows from its outer child plan and searches the hash table for each one.
518 </para>
519
520 <para>
521 Another possible type of join is a merge join, illustrated here:
522
523 <screen>
524 EXPLAIN SELECT *
525 FROM tenk1 t1, onek t2
526 WHERE t1.unique1 &lt; 100 AND t1.unique2 = t2.unique2;
527
528 QUERY PLAN
529 -------------------------------------------------------------------&zwsp;-----------------------
530 Merge Join (cost=0.56..233.49 rows=10 width=488)
531 Merge Cond: (t1.unique2 = t2.unique2)
532 -&gt; Index Scan using tenk1_unique2 on tenk1 t1 (cost=0.29..643.28 rows=100 width=244)
533 Filter: (unique1 &lt; 100)
534 -&gt; Index Scan using onek_unique2 on onek t2 (cost=0.28..166.28 rows=1000 width=244)
535 </screen>
536 </para>
537
538 <para>
539 Merge join requires its input data to be sorted on the join keys. In this
540 example each input is sorted by using an index scan to visit the rows
541 in the correct order; but a sequential scan and sort could also be used.
542 (Sequential-scan-and-sort frequently beats an index scan for sorting many rows,
543 because of the nonsequential disk access required by the index scan.)
544 </para>
545
546 <para>
547 One way to look at variant plans is to force the planner to disregard
548 whatever strategy it thought was the cheapest, using the enable/disable
549 flags described in <xref linkend="runtime-config-query-enable"/>.
550 (This is a crude tool, but useful. See
551 also <xref linkend="explicit-joins"/>.)
552 For example, if we're unconvinced that merge join is the best join
553 type for the previous example, we could try
554
555 <screen>
556 SET enable_mergejoin = off;
557
558 EXPLAIN SELECT *
559 FROM tenk1 t1, onek t2
560 WHERE t1.unique1 &lt; 100 AND t1.unique2 = t2.unique2;
561
562 QUERY PLAN
563 -------------------------------------------------------------------&zwsp;-----------------------
564 Hash Join (cost=226.23..344.08 rows=10 width=488)
565 Hash Cond: (t2.unique2 = t1.unique2)
566 -&gt; Seq Scan on onek t2 (cost=0.00..114.00 rows=1000 width=244)
567 -&gt; Hash (cost=224.98..224.98 rows=100 width=244)
568 -&gt; Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)
569 Recheck Cond: (unique1 &lt; 100)
570 -&gt; Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
571 Index Cond: (unique1 &lt; 100)
572 </screen>
573
574 which shows that the planner thinks that hash join would be nearly 50%
575 more expensive than merge join for this case.
576 Of course, the next question is whether it's right about that.
577 We can investigate that using <command>EXPLAIN ANALYZE</command>, as
578 discussed <link linkend="using-explain-analyze">below</link>.
579 </para>
580
581 <para>
582 When using the enable/disable flags to disable plan node types, many of
583 the flags only discourage the use of the corresponding plan node and don't
584 outright disallow the planner's ability to use the plan node type. This
585 is by design so that the planner still maintains the ability to form a
586 plan for a given query. When the resulting plan contains a disabled node,
587 the <command>EXPLAIN</command> output will indicate this fact.
588
589 <screen>
590 SET enable_seqscan = off;
591 EXPLAIN SELECT * FROM unit;
592
593 QUERY PLAN
594 ---------------------------------------------------------
595 Seq Scan on unit (cost=0.00..21.30 rows=1130 width=44)
596 Disabled: true
597 </screen>
598 </para>
599
600 <para>
601 Because the <literal>unit</literal> table has no indexes, there is no
602 other means to read the table data, so the sequential scan is the only
603 option available to the query planner.
604 </para>
605
606 <para>
607 <indexterm>
608 <primary>subplan</primary>
609 </indexterm>
610 Some query plans involve <firstterm>subplans</firstterm>, which arise
611 from sub-<literal>SELECT</literal>s in the original query. Such
612 queries can sometimes be transformed into ordinary join plans, but
613 when they cannot be, we get plans like:
614
615 <screen>
616 EXPLAIN VERBOSE SELECT unique1
617 FROM tenk1 t
618 WHERE t.ten &lt; ALL (SELECT o.ten FROM onek o WHERE o.four = t.four);
619
620 QUERY PLAN
621 -------------------------------------------------------------------&zwsp;------
622 Seq Scan on public.tenk1 t (cost=0.00..586095.00 rows=5000 width=4)
623 Output: t.unique1
624 Filter: (ALL (t.ten &lt; (SubPlan 1).col1))
625 SubPlan 1
626 -&gt; Seq Scan on public.onek o (cost=0.00..116.50 rows=250 width=4)
627 Output: o.ten
628 Filter: (o.four = t.four)
629 </screen>
630
631 This rather artificial example serves to illustrate a couple of
632 points: values from the outer plan level can be passed down into a
633 subplan (here, <literal>t.four</literal> is passed down) and the
634 results of the sub-select are available to the outer plan. Those
635 result values are shown by <command>EXPLAIN</command> with notations
636 like
637 <literal>(<replaceable>subplan_name</replaceable>).col<replaceable>N</replaceable></literal>,
638 which refers to the <replaceable>N</replaceable>'th output column of
639 the sub-<literal>SELECT</literal>.
640 </para>
641
642 <para>
643 <indexterm>
644 <primary>subplan</primary>
645 <secondary>hashed</secondary>
646 </indexterm>
647 In the example above, the <literal>ALL</literal> operator runs the
648 subplan again for each row of the outer query (which accounts for the
649 high estimated cost). Some queries can use a <firstterm>hashed
650 subplan</firstterm> to avoid that:
651
652 <screen>
653 EXPLAIN SELECT *
654 FROM tenk1 t
655 WHERE t.unique1 NOT IN (SELECT o.unique1 FROM onek o);
656
657 QUERY PLAN
658 -------------------------------------------------------------------&zwsp;-------------------------
659 Seq Scan on tenk1 t (cost=61.77..531.77 rows=5000 width=244)
660 Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1)))
661 SubPlan 1
662 -&gt; Index Only Scan using onek_unique1 on onek o (cost=0.28..59.27 rows=1000 width=4)
663 (4 rows)
664 </screen>
665
666 Here, the subplan is run a single time and its output is loaded into
667 an in-memory hash table, which is then probed by the
668 outer <literal>ANY</literal> operator. This requires that the
669 sub-<literal>SELECT</literal> not reference any variables of the outer
670 query, and that the <literal>ANY</literal>'s comparison operator be
671 amenable to hashing.
672 </para>
673
674 <para>
675 <indexterm>
676 <primary>initplan</primary>
677 </indexterm>
678 If, in addition to not referencing any variables of the outer query,
679 the sub-<literal>SELECT</literal> cannot return more than one row,
680 it may instead be implemented as an <firstterm>initplan</firstterm>:
681
682 <screen>
683 EXPLAIN VERBOSE SELECT unique1
684 FROM tenk1 t1 WHERE t1.ten = (SELECT (random() * 10)::integer);
685
686 QUERY PLAN
687 ------------------------------------------------------------&zwsp;--------
688 Seq Scan on public.tenk1 t1 (cost=0.02..470.02 rows=1000 width=4)
689 Output: t1.unique1
690 Filter: (t1.ten = (InitPlan 1).col1)
691 InitPlan 1
692 -&gt; Result (cost=0.00..0.02 rows=1 width=4)
693 Output: ((random() * '10'::double precision))::integer
694 </screen>
695
696 An initplan is run only once per execution of the outer plan, and its
697 results are saved for re-use in later rows of the outer plan. So in
698 this example <literal>random()</literal> is evaluated only once and
699 all the values of <literal>t1.ten</literal> are compared to the same
700 randomly-chosen integer. That's quite different from what would
701 happen without the sub-<literal>SELECT</literal> construct.
702 </para>
703
704 </sect2>
705
706 <sect2 id="using-explain-analyze">
707 <title><command>EXPLAIN ANALYZE</command></title>
708
709 <para>
710 It is possible to check the accuracy of the planner's estimates
711 by using <command>EXPLAIN</command>'s <literal>ANALYZE</literal> option. With this
712 option, <command>EXPLAIN</command> actually executes the query, and then displays
713 the true row counts and true run time accumulated within each plan node,
714 along with the same estimates that a plain <command>EXPLAIN</command>
715 shows. For example, we might get a result like this:
716
717 <screen>
718 EXPLAIN ANALYZE SELECT *
719 FROM tenk1 t1, tenk2 t2
720 WHERE t1.unique1 &lt; 10 AND t1.unique2 = t2.unique2;
721
722 QUERY PLAN
723 -------------------------------------------------------------------&zwsp;--------------------------------------------------------------
724 Nested Loop (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10.00 loops=1)
725 Buffers: shared hit=36 read=6
726 -&gt; 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)
727 Recheck Cond: (unique1 &lt; 10)
728 Heap Blocks: exact=10
729 Buffers: shared hit=3 read=5 written=4
730 -&gt; 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)
731 Index Cond: (unique1 &lt; 10)
732 Index Searches: 1
733 Buffers: shared hit=2
734 -&gt; 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)
735 Index Cond: (unique2 = t1.unique2)
736 Index Searches: 10
737 Buffers: shared hit=24 read=6
738 Planning:
739 Buffers: shared hit=15 dirtied=9
740 Planning Time: 0.485 ms
741 Execution Time: 0.073 ms
742 </screen>
743
744 Note that the <quote>actual time</quote> values are in milliseconds of
745 real time, whereas the <literal>cost</literal> estimates are expressed in
746 arbitrary units; so they are unlikely to match up.
747 The thing that's usually most important to look for is whether the
748 estimated row counts are reasonably close to reality. In this example
749 the estimates were all dead-on, but that's quite unusual in practice.
750 </para>
751
752 <para>
753 In some query plans, it is possible for a subplan node to be executed more
754 than once. For example, the inner index scan will be executed once per
755 outer row in the above nested-loop plan. In such cases, the
756 <literal>loops</literal> value reports the
757 total number of executions of the node, and the actual time and rows
758 values shown are averages per-execution. This is done to make the numbers
759 comparable with the way that the cost estimates are shown. Multiply by
760 the <literal>loops</literal> value to get the total time actually spent in
761 the node. In the above example, we spent a total of 0.030 milliseconds
762 executing the index scans on <literal>tenk2</literal>.
763 </para>
764
765 <para>
766 In some cases <command>EXPLAIN ANALYZE</command> shows additional execution
767 statistics beyond the plan node execution times and row counts.
768 For example, Sort and Hash nodes provide extra information:
769
770 <screen>
771 EXPLAIN ANALYZE SELECT *
772 FROM tenk1 t1, tenk2 t2
773 WHERE t1.unique1 &lt; 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;
774
775 QUERY PLAN
776 -------------------------------------------------------------------&zwsp;-------------------------------------------------------------------&zwsp;------
777 Sort (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100.00 loops=1)
778 Sort Key: t1.fivethous
779 Sort Method: quicksort Memory: 74kB
780 Buffers: shared hit=440
781 -&gt; Hash Join (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100.00 loops=1)
782 Hash Cond: (t2.unique2 = t1.unique2)
783 Buffers: shared hit=437
784 -&gt; 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)
785 Buffers: shared hit=345
786 -&gt; Hash (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100.00 loops=1)
787 Buckets: 1024 Batches: 1 Memory Usage: 35kB
788 Buffers: shared hit=92
789 -&gt; 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)
790 Recheck Cond: (unique1 &lt; 100)
791 Heap Blocks: exact=90
792 Buffers: shared hit=92
793 -&gt; 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)
794 Index Cond: (unique1 &lt; 100)
795 Index Searches: 1
796 Buffers: shared hit=2
797 Planning:
798 Buffers: shared hit=12
799 Planning Time: 0.187 ms
800 Execution Time: 3.036 ms
801 </screen>
802
803 The Sort node shows the sort method used (in particular, whether the sort
804 was in-memory or on-disk) and the amount of memory or disk space needed.
805 The Hash node shows the number of hash buckets and batches as well as the
806 peak amount of memory used for the hash table. (If the number of batches
807 exceeds one, there will also be disk space usage involved, but that is not
808 shown.)
809 </para>
810
811 <para>
812 Index Scan nodes (as well as Bitmap Index Scan and Index-Only Scan nodes)
813 show an <quote>Index Searches</quote> line that reports the total number
814 of searches across <emphasis>all</emphasis> node
815 executions/<literal>loops</literal>:
816
817 <screen>
818 EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE thousand IN (1, 500, 700, 999);
819 QUERY PLAN
820 -------------------------------------------------------------------&zwsp;---------------------------------------------------------------
821 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)
822 Recheck Cond: (thousand = ANY ('{1,500,700,999}'::integer[]))
823 Heap Blocks: exact=39
824 Buffers: shared hit=47
825 -> 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)
826 Index Cond: (thousand = ANY ('{1,500,700,999}'::integer[]))
827 Index Searches: 4
828 Buffers: shared hit=8
829 Planning Time: 0.029 ms
830 Execution Time: 0.034 ms
831 </screen>
832
833 Here we see a Bitmap Index Scan node that needed 4 separate index
834 searches. The scan had to search the index from the
835 <structname>tenk1_thous_tenthous</structname> index root page once per
836 <type>integer</type> value from the predicate's <literal>IN</literal>
837 construct. However, the number of index searches often won't have such a
838 simple correspondence to the query predicate:
839
840 <screen>
841 EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE thousand IN (1, 2, 3, 4);
842 QUERY PLAN
843 -------------------------------------------------------------------&zwsp;---------------------------------------------------------------
844 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)
845 Recheck Cond: (thousand = ANY ('{1,2,3,4}'::integer[]))
846 Heap Blocks: exact=38
847 Buffers: shared hit=40
848 -> 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)
849 Index Cond: (thousand = ANY ('{1,2,3,4}'::integer[]))
850 Index Searches: 1
851 Buffers: shared hit=2
852 Planning Time: 0.029 ms
853 Execution Time: 0.026 ms
854 </screen>
855
856 This variant of our <literal>IN</literal> query performed only 1 index
857 search. It spent less time traversing the index (compared to the original
858 query) because its <literal>IN</literal> construct uses values matching
859 index tuples stored next to each other, on the same
860 <structname>tenk1_thous_tenthous</structname> index leaf page.
861 </para>
862
863 <para>
864 The <quote>Index Searches</quote> line is also useful with B-tree index
865 scans that apply the <firstterm>skip scan</firstterm> optimization to
866 more efficiently traverse through an index:
867 <screen>
868 EXPLAIN ANALYZE SELECT four, unique1 FROM tenk1 WHERE four BETWEEN 1 AND 3 AND unique1 = 42;
869 QUERY PLAN
870 -------------------------------------------------------------------&zwsp;---------------------------------------------------------------
871 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)
872 Index Cond: ((four &gt;= 1) AND (four &lt;= 3) AND (unique1 = 42))
873 Heap Fetches: 0
874 Index Searches: 3
875 Buffers: shared hit=7
876 Planning Time: 0.029 ms
877 Execution Time: 0.012 ms
878 </screen>
879
880 Here we see an Index-Only Scan node using
881 <structname>tenk1_four_unique1_idx</structname>, a multi-column index on the
882 <structname>tenk1</structname> table's <structfield>four</structfield> and
883 <structfield>unique1</structfield> columns. The scan performs 3 searches
884 that each read a single index leaf page:
885 <quote><literal>four = 1 AND unique1 = 42</literal></quote>,
886 <quote><literal>four = 2 AND unique1 = 42</literal></quote>, and
887 <quote><literal>four = 3 AND unique1 = 42</literal></quote>. This index
888 is generally a good target for skip scan, since, as discussed in
889 <xref linkend="indexes-multicolumn"/>, its leading column (the
890 <structfield>four</structfield> column) contains only 4 distinct values,
891 while its second/final column (the <structfield>unique1</structfield>
892 column) contains many distinct values.
893 </para>
894
895 <para>
896 Another type of extra information is the number of rows removed by a
897 filter condition:
898
899 <screen>
900 EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten &lt; 7;
901
902 QUERY PLAN
903 -------------------------------------------------------------------&zwsp;--------------------------------------
904 Seq Scan on tenk1 (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000.00 loops=1)
905 Filter: (ten &lt; 7)
906 Rows Removed by Filter: 3000
907 Buffers: shared hit=345
908 Planning Time: 0.102 ms
909 Execution Time: 2.145 ms
910 </screen>
911
912 These counts can be particularly valuable for filter conditions applied at
913 join nodes. The <quote>Rows Removed</quote> line only appears when at least
914 one scanned row, or potential join pair in the case of a join node,
915 is rejected by the filter condition.
916 </para>
917
918 <para>
919 A case similar to filter conditions occurs with <quote>lossy</quote>
920 index scans. For example, consider this search for polygons containing a
921 specific point:
922
923 <screen>
924 EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @&gt; polygon '(0.5,2.0)';
925
926 QUERY PLAN
927 -------------------------------------------------------------------&zwsp;-----------------------------------
928 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)
929 Filter: (f1 @&gt; '((0.5,2))'::polygon)
930 Rows Removed by Filter: 7
931 Buffers: shared hit=1
932 Planning Time: 0.039 ms
933 Execution Time: 0.033 ms
934 </screen>
935
936 The planner thinks (quite correctly) that this sample table is too small
937 to bother with an index scan, so we have a plain sequential scan in which
938 all the rows got rejected by the filter condition. But if we force an
939 index scan to be used, we see:
940
941 <screen>
942 SET enable_seqscan TO off;
943
944 EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @&gt; polygon '(0.5,2.0)';
945
946 QUERY PLAN
947 -------------------------------------------------------------------&zwsp;-------------------------------------------------------
948 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)
949 Index Cond: (f1 @&gt; '((0.5,2))'::polygon)
950 Rows Removed by Index Recheck: 1
951 Index Searches: 1
952 Buffers: shared hit=1
953 Planning Time: 0.039 ms
954 Execution Time: 0.098 ms
955 </screen>
956
957 Here we can see that the index returned one candidate row, which was
958 then rejected by a recheck of the index condition. This happens because a
959 GiST index is <quote>lossy</quote> for polygon containment tests: it actually
960 returns the rows with polygons that overlap the target, and then we have
961 to do the exact containment test on those rows.
962 </para>
963
964 <para>
965 <command>EXPLAIN</command> has a <literal>BUFFERS</literal> option which
966 provides additional detail about I/O operations performed during the
967 planning and execution of the given query. The buffer numbers displayed
968 show the count of the non-distinct buffers hit, read, dirtied, and written
969 for the given node and all of its child nodes. The
970 <literal>ANALYZE</literal> option implicitly enables the
971 <literal>BUFFERS</literal> option. If this
972 is undesired, <literal>BUFFERS</literal> may be explicitly disabled:
973
974 <screen>
975 EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM tenk1 WHERE unique1 &lt; 100 AND unique2 &gt; 9000;
976
977 QUERY PLAN
978 -------------------------------------------------------------------&zwsp;--------------------------------------------------------------
979 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)
980 Recheck Cond: ((unique1 &lt; 100) AND (unique2 &gt; 9000))
981 Heap Blocks: exact=10
982 -&gt; BitmapAnd (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0.00 loops=1)
983 -&gt; 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)
984 Index Cond: (unique1 &lt; 100)
985 Index Searches: 1
986 -&gt; 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)
987 Index Cond: (unique2 &gt; 9000)
988 Index Searches: 1
989 Planning Time: 0.162 ms
990 Execution Time: 0.143 ms
991 </screen>
992 </para>
993
994 <para>
995 Keep in mind that because <command>EXPLAIN ANALYZE</command> actually
996 runs the query, any side-effects will happen as usual, even though
997 whatever results the query might output are discarded in favor of
998 printing the <command>EXPLAIN</command> data. If you want to analyze a
999 data-modifying query without changing your tables, you can
1000 roll the command back afterwards, for example:
1001
1002 <screen>
1003 BEGIN;
1004
1005 EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 &lt; 100;
1006
1007 QUERY PLAN
1008 -------------------------------------------------------------------&zwsp;-------------------------------------------------------------
1009 Update on tenk1 (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0.00 loops=1)
1010 -&gt; 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)
1011 Recheck Cond: (unique1 &lt; 100)
1012 Heap Blocks: exact=90
1013 Buffers: shared hit=4 read=2
1014 -&gt; 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)
1015 Index Cond: (unique1 &lt; 100)
1016 Index Searches: 1
1017 Buffers: shared read=2
1018 Planning Time: 0.151 ms
1019 Execution Time: 1.856 ms
1020
1021 ROLLBACK;
1022 </screen>
1023 </para>
1024
1025 <para>
1026 As seen in this example, when the query is an <command>INSERT</command>,
1027 <command>UPDATE</command>, <command>DELETE</command>, or
1028 <command>MERGE</command> command, the actual work of
1029 applying the table changes is done by a top-level Insert, Update,
1030 Delete, or Merge plan node. The plan nodes underneath this node perform
1031 the work of locating the old rows and/or computing the new data.
1032 So above, we see the same sort of bitmap table scan we've seen already,
1033 and its output is fed to an Update node that stores the updated rows.
1034 It's worth noting that although the data-modifying node can take a
1035 considerable amount of run time (here, it's consuming the lion's share
1036 of the time), the planner does not currently add anything to the cost
1037 estimates to account for that work. That's because the work to be done is
1038 the same for every correct query plan, so it doesn't affect planning
1039 decisions.
1040 </para>
1041
1042 <para>
1043 When an <command>UPDATE</command>, <command>DELETE</command>, or
1044 <command>MERGE</command> command affects a partitioned table or
1045 inheritance hierarchy, the output might look like this:
1046
1047 <screen>
1048 EXPLAIN UPDATE gtest_parent SET f1 = CURRENT_DATE WHERE f2 = 101;
1049
1050 QUERY PLAN
1051 -------------------------------------------------------------------&zwsp;---------------------
1052 Update on gtest_parent (cost=0.00..3.06 rows=0 width=0)
1053 Update on gtest_child gtest_parent_1
1054 Update on gtest_child2 gtest_parent_2
1055 Update on gtest_child3 gtest_parent_3
1056 -&gt; Append (cost=0.00..3.06 rows=3 width=14)
1057 -&gt; Seq Scan on gtest_child gtest_parent_1 (cost=0.00..1.01 rows=1 width=14)
1058 Filter: (f2 = 101)
1059 -&gt; Seq Scan on gtest_child2 gtest_parent_2 (cost=0.00..1.01 rows=1 width=14)
1060 Filter: (f2 = 101)
1061 -&gt; Seq Scan on gtest_child3 gtest_parent_3 (cost=0.00..1.01 rows=1 width=14)
1062 Filter: (f2 = 101)
1063 </screen>
1064
1065 In this example the Update node needs to consider three child tables,
1066 but not the originally-mentioned partitioned table (since that never
1067 stores any data). So there are three input
1068 scanning subplans, one per table. For clarity, the Update node is
1069 annotated to show the specific target tables that will be updated, in the
1070 same order as the corresponding subplans.
1071 </para>
1072
1073 <para>
1074 The <literal>Planning time</literal> shown by <command>EXPLAIN
1075 ANALYZE</command> is the time it took to generate the query plan from the
1076 parsed query and optimize it. It does not include parsing or rewriting.
1077 </para>
1078
1079 <para>
1080 The <literal>Execution time</literal> shown by <command>EXPLAIN
1081 ANALYZE</command> includes executor start-up and shut-down time, as well
1082 as the time to run any triggers that are fired, but it does not include
1083 parsing, rewriting, or planning time.
1084 Time spent executing <literal>BEFORE</literal> triggers, if any, is included in
1085 the time for the related Insert, Update, or Delete node; but time
1086 spent executing <literal>AFTER</literal> triggers is not counted there because
1087 <literal>AFTER</literal> triggers are fired after completion of the whole plan.
1088 The total time spent in each trigger
1089 (either <literal>BEFORE</literal> or <literal>AFTER</literal>) is also shown separately.
1090 Note that deferred constraint triggers will not be executed
1091 until end of transaction and are thus not considered at all by
1092 <command>EXPLAIN ANALYZE</command>.
1093 </para>
1094
1095 <para>
1096 The time shown for the top-level node does not include any time needed
1097 to convert the query's output data into displayable form or to send it
1098 to the client. While <command>EXPLAIN ANALYZE</command> will never
1099 send the data to the client, it can be told to convert the query's
1100 output data to displayable form and measure the time needed for that,
1101 by specifying the <literal>SERIALIZE</literal> option. That time will
1102 be shown separately, and it's also included in the
1103 total <literal>Execution time</literal>.
1104 </para>
1105
1106 </sect2>
1107
1108 <sect2 id="using-explain-caveats">
1109 <title>Caveats</title>
1110
1111 <para>
1112 There are two significant ways in which run times measured by
1113 <command>EXPLAIN ANALYZE</command> can deviate from normal execution of
1114 the same query. First, since no output rows are delivered to the client,
1115 network transmission costs are not included. I/O conversion costs are
1116 not included either unless <literal>SERIALIZE</literal> is specified.
1117 Second, the measurement overhead added by <command>EXPLAIN
1118 ANALYZE</command> can be significant, especially on machines with slow
1119 <function>gettimeofday()</function> operating-system calls. You can use the
1120 <xref linkend="pgtesttiming"/> tool to measure the overhead of timing
1121 on your system.
1122 </para>
1123
1124 <para>
1125 <command>EXPLAIN</command> results should not be extrapolated to situations
1126 much different from the one you are actually testing; for example,
1127 results on a toy-sized table cannot be assumed to apply to large tables.
1128 The planner's cost estimates are not linear and so it might choose
1129 a different plan for a larger or smaller table. An extreme example
1130 is that on a table that only occupies one disk page, you'll nearly
1131 always get a sequential scan plan whether indexes are available or not.
1132 The planner realizes that it's going to take one disk page read to
1133 process the table in any case, so there's no value in expending additional
1134 page reads to look at an index. (We saw this happening in the
1135 <literal>polygon_tbl</literal> example above.)
1136 </para>
1137
1138 <para>
1139 There are cases in which the actual and estimated values won't match up
1140 well, but nothing is really wrong. One such case occurs when
1141 plan node execution is stopped short by a <literal>LIMIT</literal> or similar
1142 effect. For example, in the <literal>LIMIT</literal> query we used before,
1143
1144 <screen>
1145 EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 &lt; 100 AND unique2 &gt; 9000 LIMIT 2;
1146
1147 QUERY PLAN
1148 -------------------------------------------------------------------&zwsp;------------------------------------------------------------
1149 Limit (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2.00 loops=1)
1150 Buffers: shared hit=16
1151 -&gt; 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)
1152 Index Cond: (unique2 &gt; 9000)
1153 Filter: (unique1 &lt; 100)
1154 Rows Removed by Filter: 287
1155 Index Searches: 1
1156 Buffers: shared hit=16
1157 Planning Time: 0.077 ms
1158 Execution Time: 0.086 ms
1159 </screen>
1160
1161 the estimated cost and row count for the Index Scan node are shown as
1162 though it were run to completion. But in reality the Limit node stopped
1163 requesting rows after it got two, so the actual row count is only 2 and
1164 the run time is less than the cost estimate would suggest. This is not
1165 an estimation error, only a discrepancy in the way the estimates and true
1166 values are displayed.
1167 </para>
1168
1169 <para>
1170 Merge joins also have measurement artifacts that can confuse the unwary.
1171 A merge join will stop reading one input if it's exhausted the other input
1172 and the next key value in the one input is greater than the last key value
1173 of the other input; in such a case there can be no more matches and so no
1174 need to scan the rest of the first input. This results in not reading all
1175 of one child, with results like those mentioned for <literal>LIMIT</literal>.
1176 Also, if the outer (first) child contains rows with duplicate key values,
1177 the inner (second) child is backed up and rescanned for the portion of its
1178 rows matching that key value. <command>EXPLAIN ANALYZE</command> counts these
1179 repeated emissions of the same inner rows as if they were real additional
1180 rows. When there are many outer duplicates, the reported actual row count
1181 for the inner child plan node can be significantly larger than the number
1182 of rows that are actually in the inner relation.
1183 </para>
1184
1185 <para>
1186 BitmapAnd and BitmapOr nodes always report their actual row counts as zero,
1187 due to implementation limitations.
1188 </para>
1189
1190 <para>
1191 Normally, <command>EXPLAIN</command> will display every plan node
1192 created by the planner. However, there are cases where the executor
1193 can determine that certain nodes need not be executed because they
1194 cannot produce any rows, based on parameter values that were not
1195 available at planning time. (Currently this can only happen for child
1196 nodes of an Append or MergeAppend node that is scanning a partitioned
1197 table.) When this happens, those plan nodes are omitted from
1198 the <command>EXPLAIN</command> output and a <literal>Subplans
1199 Removed: <replaceable>N</replaceable></literal> annotation appears
1200 instead.
1201 </para>
1202 </sect2>
1203
1204 </sect1>
1205
1206 <sect1 id="planner-stats">
1207 <title>Statistics Used by the Planner</title>
1208
1209 <indexterm zone="planner-stats">
1210 <primary>statistics</primary>
1211 <secondary>of the planner</secondary>
1212 </indexterm>
1213
1214 <sect2 id="planner-stats-single-column">
1215 <title>Single-Column Statistics</title>
1216 <para>
1217 As we saw in the previous section, the query planner needs to estimate
1218 the number of rows retrieved by a query in order to make good choices
1219 of query plans. This section provides a quick look at the statistics
1220 that the system uses for these estimates.
1221 </para>
1222
1223 <para>
1224 One component of the statistics is the total number of entries in
1225 each table and index, as well as the number of disk blocks occupied
1226 by each table and index. This information is kept in the table
1227 <link linkend="catalog-pg-class"><structname>pg_class</structname></link>,
1228 in the columns <structfield>reltuples</structfield> and
1229 <structfield>relpages</structfield>. We can look at it with
1230 queries similar to this one:
1231
1232 <screen>
1233 SELECT relname, relkind, reltuples, relpages
1234 FROM pg_class
1235 WHERE relname LIKE 'tenk1%';
1236
1237 relname | relkind | reltuples | relpages
1238 ----------------------+---------+-----------+----------
1239 tenk1 | r | 10000 | 345
1240 tenk1_hundred | i | 10000 | 11
1241 tenk1_thous_tenthous | i | 10000 | 30
1242 tenk1_unique1 | i | 10000 | 30
1243 tenk1_unique2 | i | 10000 | 30
1244 (5 rows)
1245 </screen>
1246
1247 Here we can see that <structname>tenk1</structname> contains 10000
1248 rows, as do its indexes, but the indexes are (unsurprisingly) much
1249 smaller than the table.
1250 </para>
1251
1252 <para>
1253 For efficiency reasons, <structfield>reltuples</structfield>
1254 and <structfield>relpages</structfield> are not updated on-the-fly,
1255 and so they usually contain somewhat out-of-date values.
1256 They are updated by <command>VACUUM</command>, <command>ANALYZE</command>, and a
1257 few DDL commands such as <command>CREATE INDEX</command>. A <command>VACUUM</command>
1258 or <command>ANALYZE</command> operation that does not scan the entire table
1259 (which is commonly the case) will incrementally update the
1260 <structfield>reltuples</structfield> count on the basis of the part
1261 of the table it did scan, resulting in an approximate value.
1262 In any case, the planner
1263 will scale the values it finds in <structname>pg_class</structname>
1264 to match the current physical table size, thus obtaining a closer
1265 approximation.
1266 </para>
1267
1268 <indexterm>
1269 <primary>pg_statistic</primary>
1270 </indexterm>
1271
1272 <para>
1273 Most queries retrieve only a fraction of the rows in a table, due
1274 to <literal>WHERE</literal> clauses that restrict the rows to be
1275 examined. The planner thus needs to make an estimate of the
1276 <firstterm>selectivity</firstterm> of <literal>WHERE</literal> clauses, that is,
1277 the fraction of rows that match each condition in the
1278 <literal>WHERE</literal> clause. The information used for this task is
1279 stored in the
1280 <link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>
1281 system catalog. Entries in <structname>pg_statistic</structname>
1282 are updated by the <command>ANALYZE</command> and <command>VACUUM
1283 ANALYZE</command> commands, and are always approximate even when freshly
1284 updated.
1285 </para>
1286
1287 <indexterm>
1288 <primary>pg_stats</primary>
1289 </indexterm>
1290
1291 <para>
1292 Rather than look at <structname>pg_statistic</structname> directly,
1293 it's better to look at its view
1294 <link linkend="view-pg-stats"><structname>pg_stats</structname></link>
1295 when examining the statistics manually. <structname>pg_stats</structname>
1296 is designed to be more easily readable. Furthermore,
1297 <structname>pg_stats</structname> is readable by all, whereas
1298 <structname>pg_statistic</structname> is only readable by a superuser.
1299 (This prevents unprivileged users from learning something about
1300 the contents of other people's tables from the statistics. The
1301 <structname>pg_stats</structname> view is restricted to show only
1302 rows about tables that the current user can read.)
1303 For example, we might do:
1304
1305 <screen>
1306 SELECT attname, inherited, n_distinct,
1307 array_to_string(most_common_vals, E'\n') as most_common_vals
1308 FROM pg_stats
1309 WHERE tablename = 'road';
1310
1311 attname | inherited | n_distinct | most_common_vals
1312 ---------+-----------+------------+------------------------------------
1313 name | f | -0.5681108 | I- 580 Ramp+
1314 | | | I- 880 Ramp+
1315 | | | Sp Railroad +
1316 | | | I- 580 +
1317 | | | I- 680 Ramp+
1318 | | | I- 80 Ramp+
1319 | | | 14th St +
1320 | | | I- 880 +
1321 | | | Mac Arthur Blvd+
1322 | | | Mission Blvd+
1323 ...
1324 name | t | -0.5125 | I- 580 Ramp+
1325 | | | I- 880 Ramp+
1326 | | | I- 580 +
1327 | | | I- 680 Ramp+
1328 | | | I- 80 Ramp+
1329 | | | Sp Railroad +
1330 | | | I- 880 +
1331 | | | State Hwy 13 Ramp+
1332 | | | I- 80 +
1333 | | | State Hwy 24 Ramp+
1334 ...
1335 thepath | f | 0 |
1336 thepath | t | 0 |
1337 (4 rows)
1338 </screen>
1339
1340 Note that two rows are displayed for the same column, one corresponding
1341 to the complete inheritance hierarchy starting at the
1342 <literal>road</literal> table (<literal>inherited</literal>=<literal>t</literal>),
1343 and another one including only the <literal>road</literal> table itself
1344 (<literal>inherited</literal>=<literal>f</literal>).
1345 (For brevity, we have only shown the first ten most-common values for
1346 the <literal>name</literal> column.)
1347 </para>
1348
1349 <para>
1350 The amount of information stored in <structname>pg_statistic</structname>
1351 by <command>ANALYZE</command>, in particular the maximum number of entries in the
1352 <structfield>most_common_vals</structfield> and <structfield>histogram_bounds</structfield>
1353 arrays for each column, can be set on a
1354 column-by-column basis using the <command>ALTER TABLE SET STATISTICS</command>
1355 command, or globally by setting the
1356 <xref linkend="guc-default-statistics-target"/> configuration variable.
1357 The default limit is presently 100 entries. Raising the limit
1358 might allow more accurate planner estimates to be made, particularly for
1359 columns with irregular data distributions, at the price of consuming
1360 more space in <structname>pg_statistic</structname> and slightly more
1361 time to compute the estimates. Conversely, a lower limit might be
1362 sufficient for columns with simple data distributions.
1363 </para>
1364
1365 <para>
1366 Further details about the planner's use of statistics can be found in
1367 <xref linkend="planner-stats-details"/>.
1368 </para>
1369 </sect2>
1370
1371 <sect2 id="planner-stats-extended">
1372 <title>Extended Statistics</title>
1373
1374 <indexterm zone="planner-stats-extended">
1375 <primary>statistics</primary>
1376 <secondary>of the planner</secondary>
1377 </indexterm>
1378
1379 <indexterm>
1380 <primary>correlation</primary>
1381 <secondary>in the query planner</secondary>
1382 </indexterm>
1383
1384 <indexterm>
1385 <primary>pg_statistic_ext</primary>
1386 </indexterm>
1387
1388 <indexterm>
1389 <primary>pg_statistic_ext_data</primary>
1390 </indexterm>
1391
1392 <para>
1393 It is common to see slow queries running bad execution plans because
1394 multiple columns used in the query clauses are correlated.
1395 The planner normally assumes that multiple conditions
1396 are independent of each other,
1397 an assumption that does not hold when column values are correlated.
1398 Regular statistics, because of their per-individual-column nature,
1399 cannot capture any knowledge about cross-column correlation.
1400 However, <productname>PostgreSQL</productname> has the ability to compute
1401 <firstterm>multivariate statistics</firstterm>, which can capture
1402 such information.
1403 </para>
1404
1405 <para>
1406 Because the number of possible column combinations is very large,
1407 it's impractical to compute multivariate statistics automatically.
1408 Instead, <firstterm>extended statistics objects</firstterm>, more often
1409 called just <firstterm>statistics objects</firstterm>, can be created to instruct
1410 the server to obtain statistics across interesting sets of columns.
1411 </para>
1412
1413 <para>
1414 Statistics objects are created using the
1415 <link linkend="sql-createstatistics"><command>CREATE STATISTICS</command></link> command.
1416 Creation of such an object merely creates a catalog entry expressing
1417 interest in the statistics. Actual data collection is performed
1418 by <command>ANALYZE</command> (either a manual command, or background
1419 auto-analyze). The collected values can be examined in the
1420 <link linkend="catalog-pg-statistic-ext-data"><structname>pg_statistic_ext_data</structname></link>
1421 catalog.
1422 </para>
1423
1424 <para>
1425 <command>ANALYZE</command> computes extended statistics based on the same
1426 sample of table rows that it takes for computing regular single-column
1427 statistics. Since the sample size is increased by increasing the
1428 statistics target for the table or any of its columns (as described in
1429 the previous section), a larger statistics target will normally result in
1430 more accurate extended statistics, as well as more time spent calculating
1431 them.
1432 </para>
1433
1434 <para>
1435 The following subsections describe the kinds of extended statistics
1436 that are currently supported.
1437 </para>
1438
1439 <sect3 id="planner-stats-extended-functional-deps">
1440 <title>Functional Dependencies</title>
1441
1442 <para>
1443 The simplest kind of extended statistics tracks <firstterm>functional
1444 dependencies</firstterm>, a concept used in definitions of database normal forms.
1445 We say that column <structfield>b</structfield> is functionally dependent on
1446 column <structfield>a</structfield> if knowledge of the value of
1447 <structfield>a</structfield> is sufficient to determine the value
1448 of <structfield>b</structfield>, that is there are no two rows having the same value
1449 of <structfield>a</structfield> but different values of <structfield>b</structfield>.
1450 In a fully normalized database, functional dependencies should exist
1451 only on primary keys and superkeys. However, in practice many data sets
1452 are not fully normalized for various reasons; intentional
1453 denormalization for performance reasons is a common example.
1454 Even in a fully normalized database, there may be partial correlation
1455 between some columns, which can be expressed as partial functional
1456 dependency.
1457 </para>
1458
1459 <para>
1460 The existence of functional dependencies directly affects the accuracy
1461 of estimates in certain queries. If a query contains conditions on
1462 both the independent and the dependent column(s), the
1463 conditions on the dependent columns do not further reduce the result
1464 size; but without knowledge of the functional dependency, the query
1465 planner will assume that the conditions are independent, resulting
1466 in underestimating the result size.
1467 </para>
1468
1469 <para>
1470 To inform the planner about functional dependencies, <command>ANALYZE</command>
1471 can collect measurements of cross-column dependency. Assessing the
1472 degree of dependency between all sets of columns would be prohibitively
1473 expensive, so data collection is limited to those groups of columns
1474 appearing together in a statistics object defined with
1475 the <literal>dependencies</literal> option. It is advisable to create
1476 <literal>dependencies</literal> statistics only for column groups that are
1477 strongly correlated, to avoid unnecessary overhead in both
1478 <command>ANALYZE</command> and later query planning.
1479 </para>
1480
1481 <para>
1482 Here is an example of collecting functional-dependency statistics:
1483 <programlisting>
1484 CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes;
1485
1486 ANALYZE zipcodes;
1487
1488 SELECT stxname, stxkeys, stxddependencies
1489 FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
1490 WHERE stxname = 'stts';
1491 stxname | stxkeys | stxddependencies
1492 ---------+---------+------------------------------------------
1493 stts | 1 5 | {"1 => 5": 1.000000, "5 => 1": 0.423130}
1494 (1 row)
1495 </programlisting>
1496 Here it can be seen that column 1 (zip code) fully determines column
1497 5 (city) so the coefficient is 1.0, while city only determines zip code
1498 about 42% of the time, meaning that there are many cities (58%) that are
1499 represented by more than a single ZIP code.
1500 </para>
1501
1502 <para>
1503 When computing the selectivity for a query involving functionally
1504 dependent columns, the planner adjusts the per-condition selectivity
1505 estimates using the dependency coefficients so as not to produce
1506 an underestimate.
1507 </para>
1508
1509 <sect4 id="planner-stats-extended-functional-deps-limits">
1510 <title>Limitations of Functional Dependencies</title>
1511
1512 <para>
1513 Functional dependencies are currently only applied when considering
1514 simple equality conditions that compare columns to constant values,
1515 and <literal>IN</literal> clauses with constant values.
1516 They are not used to improve estimates for equality conditions
1517 comparing two columns or comparing a column to an expression, nor for
1518 range clauses, <literal>LIKE</literal> or any other type of condition.
1519 </para>
1520
1521 <para>
1522 When estimating with functional dependencies, the planner assumes that
1523 conditions on the involved columns are compatible and hence redundant.
1524 If they are incompatible, the correct estimate would be zero rows, but
1525 that possibility is not considered. For example, given a query like
1526 <programlisting>
1527 SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';
1528 </programlisting>
1529 the planner will disregard the <structfield>city</structfield> clause as not
1530 changing the selectivity, which is correct. However, it will make
1531 the same assumption about
1532 <programlisting>
1533 SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';
1534 </programlisting>
1535 even though there will really be zero rows satisfying this query.
1536 Functional dependency statistics do not provide enough information
1537 to conclude that, however.
1538 </para>
1539
1540 <para>
1541 In many practical situations, this assumption is usually satisfied;
1542 for example, there might be a GUI in the application that only allows
1543 selecting compatible city and ZIP code values to use in a query.
1544 But if that's not the case, functional dependencies may not be a viable
1545 option.
1546 </para>
1547 </sect4>
1548 </sect3>
1549
1550 <sect3 id="planner-stats-extended-n-distinct-counts">
1551 <title>Multivariate N-Distinct Counts</title>
1552
1553 <para>
1554 Single-column statistics store the number of distinct values in each
1555 column. Estimates of the number of distinct values when combining more
1556 than one column (for example, for <literal>GROUP BY a, b</literal>) are
1557 frequently wrong when the planner only has single-column statistical
1558 data, causing it to select bad plans.
1559 </para>
1560
1561 <para>
1562 To improve such estimates, <command>ANALYZE</command> can collect n-distinct
1563 statistics for groups of columns. As before, it's impractical to do
1564 this for every possible column grouping, so data is collected only for
1565 those groups of columns appearing together in a statistics object
1566 defined with the <literal>ndistinct</literal> option. Data will be collected
1567 for each possible combination of two or more columns from the set of
1568 listed columns.
1569 </para>
1570
1571 <para>
1572 Continuing the previous example, the n-distinct counts in a
1573 table of ZIP codes might look like the following:
1574 <programlisting>
1575 CREATE STATISTICS stts2 (ndistinct) ON city, state, zip FROM zipcodes;
1576
1577 ANALYZE zipcodes;
1578
1579 SELECT stxkeys AS k, stxdndistinct AS nd
1580 FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
1581 WHERE stxname = 'stts2';
1582 -[ RECORD 1 ]------------------------------------------------------&zwsp;--
1583 k | 1 2 5
1584 nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178}
1585 (1 row)
1586 </programlisting>
1587 This indicates that there are three combinations of columns that
1588 have 33178 distinct values: ZIP code and state; ZIP code and city;
1589 and ZIP code, city and state (the fact that they are all equal is
1590 expected given that ZIP code alone is unique in this table). On the
1591 other hand, the combination of city and state has only 27435 distinct
1592 values.
1593 </para>
1594
1595 <para>
1596 It's advisable to create <literal>ndistinct</literal> statistics objects only
1597 on combinations of columns that are actually used for grouping, and
1598 for which misestimation of the number of groups is resulting in bad
1599 plans. Otherwise, the <command>ANALYZE</command> cycles are just wasted.
1600 </para>
1601 </sect3>
1602
1603 <sect3 id="planner-stats-extended-mcv-lists">
1604 <title>Multivariate MCV Lists</title>
1605
1606 <para>
1607 Another type of statistic stored for each column are most-common value
1608 lists. This allows very accurate estimates for individual columns, but
1609 may result in significant misestimates for queries with conditions on
1610 multiple columns.
1611 </para>
1612
1613 <para>
1614 To improve such estimates, <command>ANALYZE</command> can collect MCV
1615 lists on combinations of columns. Similarly to functional dependencies
1616 and n-distinct coefficients, it's impractical to do this for every
1617 possible column grouping. Even more so in this case, as the MCV list
1618 (unlike functional dependencies and n-distinct coefficients) does store
1619 the common column values. So data is collected only for those groups
1620 of columns appearing together in a statistics object defined with the
1621 <literal>mcv</literal> option.
1622 </para>
1623
1624 <para>
1625 Continuing the previous example, the MCV list for a table of ZIP codes
1626 might look like the following (unlike for simpler types of statistics,
1627 a function is required for inspection of MCV contents):
1628
1629 <programlisting>
1630 CREATE STATISTICS stts3 (mcv) ON city, state FROM zipcodes;
1631
1632 ANALYZE zipcodes;
1633
1634 SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),
1635 pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';
1636
1637 index | values | nulls | frequency | base_frequency
1638 -------+------------------------+-------+-----------+----------------
1639 0 | {Washington, DC} | {f,f} | 0.003467 | 2.7e-05
1640 1 | {Apo, AE} | {f,f} | 0.003067 | 1.9e-05
1641 2 | {Houston, TX} | {f,f} | 0.002167 | 0.000133
1642 3 | {El Paso, TX} | {f,f} | 0.002 | 0.000113
1643 4 | {New York, NY} | {f,f} | 0.001967 | 0.000114
1644 5 | {Atlanta, GA} | {f,f} | 0.001633 | 3.3e-05
1645 6 | {Sacramento, CA} | {f,f} | 0.001433 | 7.8e-05
1646 7 | {Miami, FL} | {f,f} | 0.0014 | 6e-05
1647 8 | {Dallas, TX} | {f,f} | 0.001367 | 8.8e-05
1648 9 | {Chicago, IL} | {f,f} | 0.001333 | 5.1e-05
1649 ...
1650 (99 rows)
1651 </programlisting>
1652 This indicates that the most common combination of city and state is
1653 Washington in DC, with actual frequency (in the sample) about 0.35%.
1654 The base frequency of the combination (as computed from the simple
1655 per-column frequencies) is only 0.0027%, resulting in two orders of
1656 magnitude under-estimates.
1657 </para>
1658
1659 <para>
1660 It's advisable to create <acronym>MCV</acronym> statistics objects only
1661 on combinations of columns that are actually used in conditions together,
1662 and for which misestimation of the number of groups is resulting in bad
1663 plans. Otherwise, the <command>ANALYZE</command> and planning cycles
1664 are just wasted.
1665 </para>
1666 </sect3>
1667
1668 </sect2>
1669 </sect1>
1670
1671 <sect1 id="explicit-joins">
1672 <title>Controlling the Planner with Explicit <literal>JOIN</literal> Clauses</title>
1673
1674 <indexterm zone="explicit-joins">
1675 <primary>join</primary>
1676 <secondary>controlling the order</secondary>
1677 </indexterm>
1678
1679 <para>
1680 It is possible
1681 to control the query planner to some extent by using the explicit <literal>JOIN</literal>
1682 syntax. To see why this matters, we first need some background.
1683 </para>
1684
1685 <para>
1686 In a simple join query, such as:
1687 <programlisting>
1688 SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
1689 </programlisting>
1690 the planner is free to join the given tables in any order. For
1691 example, it could generate a query plan that joins A to B, using
1692 the <literal>WHERE</literal> condition <literal>a.id = b.id</literal>, and then
1693 joins C to this joined table, using the other <literal>WHERE</literal>
1694 condition. Or it could join B to C and then join A to that result.
1695 Or it could join A to C and then join them with B &mdash; but that
1696 would be inefficient, since the full Cartesian product of A and C
1697 would have to be formed, there being no applicable condition in the
1698 <literal>WHERE</literal> clause to allow optimization of the join. (All
1699 joins in the <productname>PostgreSQL</productname> executor happen
1700 between two input tables, so it's necessary to build up the result
1701 in one or another of these fashions.) The important point is that
1702 these different join possibilities give semantically equivalent
1703 results but might have hugely different execution costs. Therefore,
1704 the planner will explore all of them to try to find the most
1705 efficient query plan.
1706 </para>
1707
1708 <para>
1709 When a query only involves two or three tables, there aren't many join
1710 orders to worry about. But the number of possible join orders grows
1711 exponentially as the number of tables expands. Beyond ten or so input
1712 tables it's no longer practical to do an exhaustive search of all the
1713 possibilities, and even for six or seven tables planning might take an
1714 annoyingly long time. When there are too many input tables, the
1715 <productname>PostgreSQL</productname> planner will switch from exhaustive
1716 search to a <firstterm>genetic</firstterm> probabilistic search
1717 through a limited number of possibilities. (The switch-over threshold is
1718 set by the <xref linkend="guc-geqo-threshold"/> run-time
1719 parameter.)
1720 The genetic search takes less time, but it won't
1721 necessarily find the best possible plan.
1722 </para>
1723
1724 <para>
1725 When the query involves outer joins, the planner has less freedom
1726 than it does for plain (inner) joins. For example, consider:
1727 <programlisting>
1728 SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
1729 </programlisting>
1730 Although this query's restrictions are superficially similar to the
1731 previous example, the semantics are different because a row must be
1732 emitted for each row of A that has no matching row in the join of B and C.
1733 Therefore the planner has no choice of join order here: it must join
1734 B to C and then join A to that result. Accordingly, this query takes
1735 less time to plan than the previous query. In other cases, the planner
1736 might be able to determine that more than one join order is safe.
1737 For example, given:
1738 <programlisting>
1739 SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);
1740 </programlisting>
1741 it is valid to join A to either B or C first. Currently, only
1742 <literal>FULL JOIN</literal> completely constrains the join order. Most
1743 practical cases involving <literal>LEFT JOIN</literal> or <literal>RIGHT JOIN</literal>
1744 can be rearranged to some extent.
1745 </para>
1746
1747 <para>
1748 Explicit inner join syntax (<literal>INNER JOIN</literal>, <literal>CROSS
1749 JOIN</literal>, or unadorned <literal>JOIN</literal>) is semantically the same as
1750 listing the input relations in <literal>FROM</literal>, so it does not
1751 constrain the join order.
1752 </para>
1753
1754 <para>
1755 Even though most kinds of <literal>JOIN</literal> don't completely constrain
1756 the join order, it is possible to instruct the
1757 <productname>PostgreSQL</productname> query planner to treat all
1758 <literal>JOIN</literal> clauses as constraining the join order anyway.
1759 For example, these three queries are logically equivalent:
1760 <programlisting>
1761 SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
1762 SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
1763 SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
1764 </programlisting>
1765 But if we tell the planner to honor the <literal>JOIN</literal> order,
1766 the second and third take less time to plan than the first. This effect
1767 is not worth worrying about for only three tables, but it can be a
1768 lifesaver with many tables.
1769 </para>
1770
1771 <para>
1772 To force the planner to follow the join order laid out by explicit
1773 <literal>JOIN</literal>s,
1774 set the <xref linkend="guc-join-collapse-limit"/> run-time parameter to 1.
1775 (Other possible values are discussed below.)
1776 </para>
1777
1778 <para>
1779 You do not need to constrain the join order completely in order to
1780 cut search time, because it's OK to use <literal>JOIN</literal> operators
1781 within items of a plain <literal>FROM</literal> list. For example, consider:
1782 <programlisting>
1783 SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;
1784 </programlisting>
1785 With <varname>join_collapse_limit</varname> = 1, this
1786 forces the planner to join A to B before joining them to other tables,
1787 but doesn't constrain its choices otherwise. In this example, the
1788 number of possible join orders is reduced by a factor of 5.
1789 </para>
1790
1791 <para>
1792 Constraining the planner's search in this way is a useful technique
1793 both for reducing planning time and for directing the planner to a
1794 good query plan. If the planner chooses a bad join order by default,
1795 you can force it to choose a better order via <literal>JOIN</literal> syntax
1796 &mdash; assuming that you know of a better order, that is. Experimentation
1797 is recommended.
1798 </para>
1799
1800 <para>
1801 A closely related issue that affects planning time is collapsing of
1802 subqueries into their parent query. For example, consider:
1803 <programlisting>
1804 SELECT *
1805 FROM x, y,
1806 (SELECT * FROM a, b, c WHERE something) AS ss
1807 WHERE somethingelse;
1808 </programlisting>
1809 This situation might arise from use of a view that contains a join;
1810 the view's <literal>SELECT</literal> rule will be inserted in place of the view
1811 reference, yielding a query much like the above. Normally, the planner
1812 will try to collapse the subquery into the parent, yielding:
1813 <programlisting>
1814 SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
1815 </programlisting>
1816 This usually results in a better plan than planning the subquery
1817 separately. (For example, the outer <literal>WHERE</literal> conditions might be such that
1818 joining X to A first eliminates many rows of A, thus avoiding the need to
1819 form the full logical output of the subquery.) But at the same time,
1820 we have increased the planning time; here, we have a five-way join
1821 problem replacing two separate three-way join problems. Because of the
1822 exponential growth of the number of possibilities, this makes a big
1823 difference. The planner tries to avoid getting stuck in huge join search
1824 problems by not collapsing a subquery if more than <varname>from_collapse_limit</varname>
1825 <literal>FROM</literal> items would result in the parent
1826 query. You can trade off planning time against quality of plan by
1827 adjusting this run-time parameter up or down.
1828 </para>
1829
1830 <para>
1831 <xref linkend="guc-from-collapse-limit"/> and <xref
1832 linkend="guc-join-collapse-limit"/>
1833 are similarly named because they do almost the same thing: one controls
1834 when the planner will <quote>flatten out</quote> subqueries, and the
1835 other controls when it will flatten out explicit joins. Typically
1836 you would either set <varname>join_collapse_limit</varname> equal to
1837 <varname>from_collapse_limit</varname> (so that explicit joins and subqueries
1838 act similarly) or set <varname>join_collapse_limit</varname> to 1 (if you want
1839 to control join order with explicit joins). But you might set them
1840 differently if you are trying to fine-tune the trade-off between planning
1841 time and run time.
1842 </para>
1843 </sect1>
1844
1845 <sect1 id="populate">
1846 <title>Populating a Database</title>
1847
1848 <para>
1849 One might need to insert a large amount of data when first populating
1850 a database. This section contains some suggestions on how to make
1851 this process as efficient as possible.
1852 </para>
1853
1854 <sect2 id="disable-autocommit">
1855 <title>Disable Autocommit</title>
1856
1857 <indexterm>
1858 <primary>autocommit</primary>
1859 <secondary>bulk-loading data</secondary>
1860 </indexterm>
1861
1862 <para>
1863 When using multiple <command>INSERT</command>s, turn off autocommit and just do
1864 one commit at the end. (In plain
1865 SQL, this means issuing <command>BEGIN</command> at the start and
1866 <command>COMMIT</command> at the end. Some client libraries might
1867 do this behind your back, in which case you need to make sure the
1868 library does it when you want it done.) If you allow each
1869 insertion to be committed separately,
1870 <productname>PostgreSQL</productname> is doing a lot of work for
1871 each row that is added. An additional benefit of doing all
1872 insertions in one transaction is that if the insertion of one row
1873 were to fail then the insertion of all rows inserted up to that
1874 point would be rolled back, so you won't be stuck with partially
1875 loaded data.
1876 </para>
1877 </sect2>
1878
1879 <sect2 id="populate-copy-from">
1880 <title>Use <command>COPY</command></title>
1881
1882 <para>
1883 Use <link linkend="sql-copy"><command>COPY</command></link> to load
1884 all the rows in one command, instead of using a series of
1885 <command>INSERT</command> commands. The <command>COPY</command>
1886 command is optimized for loading large numbers of rows; it is less
1887 flexible than <command>INSERT</command>, but incurs significantly
1888 less overhead for large data loads. Since <command>COPY</command>
1889 is a single command, there is no need to disable autocommit if you
1890 use this method to populate a table.
1891 </para>
1892
1893 <para>
1894 If you cannot use <command>COPY</command>, it might help to use <link
1895 linkend="sql-prepare"><command>PREPARE</command></link> to create a
1896 prepared <command>INSERT</command> statement, and then use
1897 <command>EXECUTE</command> as many times as required. This avoids
1898 some of the overhead of repeatedly parsing and planning
1899 <command>INSERT</command>. Different interfaces provide this facility
1900 in different ways; look for <quote>prepared statements</quote> in the interface
1901 documentation.
1902 </para>
1903
1904 <para>
1905 Note that loading a large number of rows using
1906 <command>COPY</command> is almost always faster than using
1907 <command>INSERT</command>, even if <command>PREPARE</command> is used and
1908 multiple insertions are batched into a single transaction.
1909 </para>
1910
1911 <para>
1912 <command>COPY</command> is fastest when used within the same
1913 transaction as an earlier <command>CREATE TABLE</command> or
1914 <command>TRUNCATE</command> command. In such cases no WAL
1915 needs to be written, because in case of an error, the files
1916 containing the newly loaded data will be removed anyway.
1917 However, this consideration only applies when
1918 <xref linkend="guc-wal-level"/> is <literal>minimal</literal>
1919 as all commands must write WAL otherwise.
1920 </para>
1921
1922 </sect2>
1923
1924 <sect2 id="populate-rm-indexes">
1925 <title>Remove Indexes</title>
1926
1927 <para>
1928 If you are loading a freshly created table, the fastest method is to
1929 create the table, bulk load the table's data using
1930 <command>COPY</command>, then create any indexes needed for the
1931 table. Creating an index on pre-existing data is quicker than
1932 updating it incrementally as each row is loaded.
1933 </para>
1934
1935 <para>
1936 If you are adding large amounts of data to an existing table,
1937 it might be a win to drop the indexes,
1938 load the table, and then recreate the indexes. Of course, the
1939 database performance for other users might suffer
1940 during the time the indexes are missing. One should also think
1941 twice before dropping a unique index, since the error checking
1942 afforded by the unique constraint will be lost while the index is
1943 missing.
1944 </para>
1945 </sect2>
1946
1947 <sect2 id="populate-rm-fkeys">
1948 <title>Remove Foreign Key Constraints</title>
1949
1950 <para>
1951 Just as with indexes, a foreign key constraint can be checked
1952 <quote>in bulk</quote> more efficiently than row-by-row. So it might be
1953 useful to drop foreign key constraints, load data, and re-create
1954 the constraints. Again, there is a trade-off between data load
1955 speed and loss of error checking while the constraint is missing.
1956 </para>
1957
1958 <para>
1959 What's more, when you load data into a table with existing foreign key
1960 constraints, each new row requires an entry in the server's list of
1961 pending trigger events (since it is the firing of a trigger that checks
1962 the row's foreign key constraint). Loading many millions of rows can
1963 cause the trigger event queue to overflow available memory, leading to
1964 intolerable swapping or even outright failure of the command. Therefore
1965 it may be <emphasis>necessary</emphasis>, not just desirable, to drop and re-apply
1966 foreign keys when loading large amounts of data. If temporarily removing
1967 the constraint isn't acceptable, the only other recourse may be to split
1968 up the load operation into smaller transactions.
1969 </para>
1970 </sect2>
1971
1972 <sect2 id="populate-work-mem">
1973 <title>Increase <varname>maintenance_work_mem</varname></title>
1974
1975 <para>
1976 Temporarily increasing the <xref linkend="guc-maintenance-work-mem"/>
1977 configuration variable when loading large amounts of data can
1978 lead to improved performance. This will help to speed up <command>CREATE
1979 INDEX</command> commands and <command>ALTER TABLE ADD FOREIGN KEY</command> commands.
1980 It won't do much for <command>COPY</command> itself, so this advice is
1981 only useful when you are using one or both of the above techniques.
1982 </para>
1983 </sect2>
1984
1985 <sect2 id="populate-max-wal-size">
1986 <title>Increase <varname>max_wal_size</varname></title>
1987
1988 <para>
1989 Temporarily increasing the <xref linkend="guc-max-wal-size"/>
1990 configuration variable can also
1991 make large data loads faster. This is because loading a large
1992 amount of data into <productname>PostgreSQL</productname> will
1993 cause checkpoints to occur more often than the normal checkpoint
1994 frequency (specified by the <varname>checkpoint_timeout</varname>
1995 configuration variable). Whenever a checkpoint occurs, all dirty
1996 pages must be flushed to disk. By increasing
1997 <varname>max_wal_size</varname> temporarily during bulk
1998 data loads, the number of checkpoints that are required can be
1999 reduced.
2000 </para>
2001 </sect2>
2002
2003 <sect2 id="populate-pitr">
2004 <title>Disable WAL Archival and Streaming Replication</title>
2005
2006 <para>
2007 When loading large amounts of data into an installation that uses
2008 WAL archiving or streaming replication, it might be faster to take a
2009 new base backup after the load has completed than to process a large
2010 amount of incremental WAL data. To prevent incremental WAL logging
2011 while loading, disable archiving and streaming replication, by setting
2012 <xref linkend="guc-wal-level"/> to <literal>minimal</literal>,
2013 <xref linkend="guc-archive-mode"/> to <literal>off</literal>, and
2014 <xref linkend="guc-max-wal-senders"/> to zero.
2015 But note that changing these settings requires a server restart,
2016 and makes any base backups taken before unavailable for archive
2017 recovery and standby server, which may lead to data loss.
2018 </para>
2019
2020 <para>
2021 Aside from avoiding the time for the archiver or WAL sender to process the
2022 WAL data, doing this will actually make certain commands faster, because
2023 they do not to write WAL at all if <varname>wal_level</varname>
2024 is <literal>minimal</literal> and the current subtransaction (or top-level
2025 transaction) created or truncated the table or index they change. (They
2026 can guarantee crash safety more cheaply by doing
2027 an <function>fsync</function> at the end than by writing WAL.)
2028 </para>
2029 </sect2>
2030
2031 <sect2 id="populate-analyze">
2032 <title>Run <command>ANALYZE</command> Afterwards</title>
2033
2034 <para>
2035 Whenever you have significantly altered the distribution of data
2036 within a table, running <link linkend="sql-analyze"><command>ANALYZE</command></link> is strongly recommended. This
2037 includes bulk loading large amounts of data into the table. Running
2038 <command>ANALYZE</command> (or <command>VACUUM ANALYZE</command>)
2039 ensures that the planner has up-to-date statistics about the
2040 table. With no statistics or obsolete statistics, the planner might
2041 make poor decisions during query planning, leading to poor
2042 performance on any tables with inaccurate or nonexistent
2043 statistics. Note that if the autovacuum daemon is enabled, it might
2044 run <command>ANALYZE</command> automatically; see
2045 <xref linkend="vacuum-for-statistics"/>
2046 and <xref linkend="autovacuum"/> for more information.
2047 </para>
2048 </sect2>
2049
2050 <sect2 id="populate-pg-dump">
2051 <title>Some Notes about <application>pg_dump</application></title>
2052
2053 <para>
2054 Dump scripts generated by <application>pg_dump</application> automatically apply
2055 several, but not all, of the above guidelines. To restore a
2056 <application>pg_dump</application> dump as quickly as possible, you need to
2057 do a few extra things manually. (Note that these points apply while
2058 <emphasis>restoring</emphasis> a dump, not while <emphasis>creating</emphasis> it.
2059 The same points apply whether loading a text dump with
2060 <application>psql</application> or using <application>pg_restore</application> to load
2061 from a <application>pg_dump</application> archive file.)
2062 </para>
2063
2064 <para>
2065 By default, <application>pg_dump</application> uses <command>COPY</command>, and when
2066 it is generating a complete schema-and-data dump, it is careful to
2067 load data before creating indexes and foreign keys. So in this case
2068 several guidelines are handled automatically. What is left
2069 for you to do is to:
2070 <itemizedlist>
2071 <listitem>
2072 <para>
2073 Set appropriate (i.e., larger than normal) values for
2074 <varname>maintenance_work_mem</varname> and
2075 <varname>max_wal_size</varname>.
2076 </para>
2077 </listitem>
2078 <listitem>
2079 <para>
2080 If using WAL archiving or streaming replication, consider disabling
2081 them during the restore. To do that, set <varname>archive_mode</varname>
2082 to <literal>off</literal>,
2083 <varname>wal_level</varname> to <literal>minimal</literal>, and
2084 <varname>max_wal_senders</varname> to zero before loading the dump.
2085 Afterwards, set them back to the right values and take a fresh
2086 base backup.
2087 </para>
2088 </listitem>
2089 <listitem>
2090 <para>
2091 Experiment with the parallel dump and restore modes of both
2092 <application>pg_dump</application> and <application>pg_restore</application> and find the
2093 optimal number of concurrent jobs to use. Dumping and restoring in
2094 parallel by means of the <option>-j</option> option should give you a
2095 significantly higher performance over the serial mode.
2096 </para>
2097 </listitem>
2098 <listitem>
2099 <para>
2100 Consider whether the whole dump should be restored as a single
2101 transaction. To do that, pass the <option>-1</option> or
2102 <option>--single-transaction</option> command-line option to
2103 <application>psql</application> or <application>pg_restore</application>. When using this
2104 mode, even the smallest of errors will rollback the entire restore,
2105 possibly discarding many hours of processing. Depending on how
2106 interrelated the data is, that might seem preferable to manual cleanup,
2107 or not. <command>COPY</command> commands will run fastest if you use a single
2108 transaction and have WAL archiving turned off.
2109 </para>
2110 </listitem>
2111 <listitem>
2112 <para>
2113 If multiple CPUs are available in the database server, consider using
2114 <application>pg_restore</application>'s <option>--jobs</option> option. This
2115 allows concurrent data loading and index creation.
2116 </para>
2117 </listitem>
2118 <listitem>
2119 <para>
2120 Run <command>ANALYZE</command> afterwards.
2121 </para>
2122 </listitem>
2123 </itemizedlist>
2124 </para>
2125
2126 <para>
2127 A data-only dump will still use <command>COPY</command>, but it does not
2128 drop or recreate indexes, and it does not normally touch foreign
2129 keys.
2130
2131 <footnote>
2132 <para>
2133 You can get the effect of disabling foreign keys by using
2134 the <option>--disable-triggers</option> option &mdash; but realize that
2135 that eliminates, rather than just postpones, foreign key
2136 validation, and so it is possible to insert bad data if you use it.
2137 </para>
2138 </footnote>
2139
2140 So when loading a data-only dump, it is up to you to drop and recreate
2141 indexes and foreign keys if you wish to use those techniques.
2142 It's still useful to increase <varname>max_wal_size</varname>
2143 while loading the data, but don't bother increasing
2144 <varname>maintenance_work_mem</varname>; rather, you'd do that while
2145 manually recreating indexes and foreign keys afterwards.
2146 And don't forget to <command>ANALYZE</command> when you're done; see
2147 <xref linkend="vacuum-for-statistics"/>
2148 and <xref linkend="autovacuum"/> for more information.
2149 </para>
2150 </sect2>
2151 </sect1>
2152
2153 <sect1 id="non-durability">
2154 <title>Non-Durable Settings</title>
2155
2156 <indexterm zone="non-durability">
2157 <primary>non-durable</primary>
2158 </indexterm>
2159
2160 <para>
2161 Durability is a database feature that guarantees the recording of
2162 committed transactions even if the server crashes or loses
2163 power. However, durability adds significant database overhead,
2164 so if your site does not require such a guarantee,
2165 <productname>PostgreSQL</productname> can be configured to run
2166 much faster. The following are configuration changes you can make
2167 to improve performance in such cases. Except as noted below, durability
2168 is still guaranteed in case of a crash of the database software;
2169 only an abrupt operating system crash creates a risk of data loss
2170 or corruption when these settings are used.
2171
2172 <itemizedlist>
2173 <listitem>
2174 <para>
2175 Place the database cluster's data directory in a memory-backed
2176 file system (i.e., <acronym>RAM</acronym> disk). This eliminates all
2177 database disk I/O, but limits data storage to the amount of
2178 available memory (and perhaps swap).
2179 </para>
2180 </listitem>
2181
2182 <listitem>
2183 <para>
2184 Turn off <xref linkend="guc-fsync"/>; there is no need to flush
2185 data to disk.
2186 </para>
2187 </listitem>
2188
2189 <listitem>
2190 <para>
2191 Turn off <xref linkend="guc-synchronous-commit"/>; there might be no
2192 need to force <acronym>WAL</acronym> writes to disk on every
2193 commit. This setting does risk transaction loss (though not data
2194 corruption) in case of a crash of the <emphasis>database</emphasis>.
2195 </para>
2196 </listitem>
2197
2198 <listitem>
2199 <para>
2200 Turn off <xref linkend="guc-full-page-writes"/>; there is no need
2201 to guard against partial page writes.
2202 </para>
2203 </listitem>
2204
2205 <listitem>
2206 <para>
2207 Increase <xref linkend="guc-max-wal-size"/> and <xref
2208 linkend="guc-checkpoint-timeout"/>; this reduces the frequency
2209 of checkpoints, but increases the storage requirements of
2210 <filename>/pg_wal</filename>.
2211 </para>
2212 </listitem>
2213
2214 <listitem>
2215 <para>
2216 Create <link linkend="sql-createtable-unlogged">unlogged
2217 tables</link> to avoid <acronym>WAL</acronym> writes, though it
2218 makes the tables non-crash-safe.
2219 </para>
2220 </listitem>
2221
2222 </itemizedlist>
2223 </para>
2224 </sect1>
2225
2226 </chapter>