1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
5 >Performance Tips</TITLE
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
15 TITLE="The SQL Language"
18 TITLE="Locking and Indexes"
19 HREF="locking-indexes.html"><LINK
21 TITLE="Statistics Used by the Planner"
22 HREF="planner-stats.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
51 HREF="locking-indexes.html"
81 HREF="planner-stats.html"
94 NAME="PERFORMANCE-TIPS"
96 >Chapter 13. Performance Tips</H1
102 >Table of Contents</B
106 HREF="performance-tips.html#USING-EXPLAIN"
114 HREF="planner-stats.html"
115 >Statistics Used by the Planner</A
119 HREF="explicit-joins.html"
120 >Controlling the Planner with Explicit <TT
128 >Populating a Database</A
134 HREF="populate.html#DISABLE-AUTOCOMMIT"
135 >Disable Autocommit</A
139 HREF="populate.html#POPULATE-COPY-FROM"
147 HREF="populate.html#POPULATE-RM-INDEXES"
152 HREF="populate.html#POPULATE-SORT-MEM"
160 HREF="populate.html#POPULATE-ANALYZE"
171 > Query performance can be affected by many things. Some of these can
172 be manipulated by the user, while others are fundamental to the underlying
173 design of the system. This chapter provides some hints about understanding
204 > for each query it is given. Choosing the right
205 plan to match the query structure and the properties of the data
206 is absolutely critical for good performance. You can use the
210 > command to see what query plan the system
211 creates for any query.
212 Plan-reading is an art that deserves an extensive tutorial, which
213 this is not; but here is some basic information.
216 > The numbers that are currently quoted by <TT
226 > Estimated start-up cost (Time expended before output scan can start,
227 e.g., time to do the sorting in a sort node.)
232 > Estimated total cost (If all rows were to be retrieved, which they may not
233 be: a query with a <TT
236 > clause will stop short of paying the total cost,
242 > Estimated number of rows output by this plan node (Again, only if
243 executed to completion)
248 > Estimated average width (in bytes) of rows output by this plan
256 > The costs are measured in units of disk page fetches. (CPU effort
257 estimates are converted into disk-page units using some
258 fairly arbitrary fudge factors. If you want to experiment with these
259 factors, see the list of run-time configuration parameters in
261 HREF="runtime-config.html#RUNTIME-CONFIG-RESOURCE"
266 > It's important to note that the cost of an upper-level node includes
267 the cost of all its child nodes. It's also important to realize that
268 the cost only reflects things that the planner/optimizer cares about.
269 In particular, the cost does not consider the time spent transmitting
270 result rows to the frontend, which could be a pretty dominant
271 factor in the true elapsed time; but the planner ignores it because
272 it cannot change it by altering the plan. (Every correct plan will
273 output the same row set, we trust.)
276 > Rows output is a little tricky because it is <SPAN
284 processed/scanned by the query, it is usually less, reflecting the
285 estimated selectivity of any <TT
288 >-clause conditions that are being
289 applied at this node. Ideally the top-level rows estimate will
290 approximate the number of rows actually returned, updated, or deleted
294 > Here are some examples (using the regression test database after a
298 >, and 7.3 development sources):
301 CLASS="PROGRAMLISTING"
302 >EXPLAIN SELECT * FROM tenk1;
305 -------------------------------------------------------------
306 Seq Scan on tenk1 (cost=0.00..333.00 rows=10000 width=148)</PRE
310 > This is about as straightforward as it gets. If you do
313 CLASS="PROGRAMLISTING"
314 >SELECT * FROM pg_class WHERE relname = 'tenk1';</PRE
317 you will find out that <CODE
321 pages and 10000 rows. So the cost is estimated at 233 page
322 reads, defined as costing 1.0 apiece, plus 10000 * <VAR
326 currently 0.01 (try <TT
328 >SHOW cpu_tuple_cost</TT
332 > Now let's modify the query to add a <TT
338 CLASS="PROGRAMLISTING"
339 >EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;
342 ------------------------------------------------------------
343 Seq Scan on tenk1 (cost=0.00..358.00 rows=1033 width=148)
344 Filter: (unique1 < 1000)</PRE
347 The estimate of output rows has gone down because of the <TT
351 However, the scan will still have to visit all 10000 rows, so the cost
352 hasn't decreased; in fact it has gone up a bit to reflect the extra CPU
353 time spent checking the <TT
359 > The actual number of rows this query would select is 1000, but the
360 estimate is only approximate. If you try to duplicate this experiment,
361 you will probably get a slightly different estimate; moreover, it will
362 change after each <TT
365 > command, because the
366 statistics produced by <TT
370 randomized sample of the table.
373 > Modify the query to restrict the condition even more:
376 CLASS="PROGRAMLISTING"
377 >EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50;
380 -------------------------------------------------------------------------------
381 Index Scan using tenk1_unique1 on tenk1 (cost=0.00..179.33 rows=49 width=148)
382 Index Cond: (unique1 < 50)</PRE
385 and you will see that if we make the <TT
388 > condition selective
389 enough, the planner will
390 eventually decide that an index scan is cheaper than a sequential scan.
391 This plan will only have to visit 50 rows because of the index,
392 so it wins despite the fact that each individual fetch is more expensive
393 than reading a whole disk page sequentially.
396 > Add another condition to the <TT
402 CLASS="PROGRAMLISTING"
403 >EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND stringu1 = 'xxx';
406 -------------------------------------------------------------------------------
407 Index Scan using tenk1_unique1 on tenk1 (cost=0.00..179.45 rows=1 width=148)
408 Index Cond: (unique1 < 50)
409 Filter: (stringu1 = 'xxx'::name)</PRE
412 The added condition <TT
414 >stringu1 = 'xxx'</TT
416 output-rows estimate, but not the cost because we still have to visit the
417 same set of rows. Notice that the <TT
421 cannot be applied as an index condition (since this index is only on
425 > column). Instead it is applied as a filter on
426 the rows retrieved by the index. Thus the cost has actually gone up
427 a little bit to reflect this extra checking.
430 > Let's try joining two tables, using the columns we have been discussing:
433 CLASS="PROGRAMLISTING"
434 >EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
437 ----------------------------------------------------------------------------
438 Nested Loop (cost=0.00..327.02 rows=49 width=296)
439 -> Index Scan using tenk1_unique1 on tenk1 t1
440 (cost=0.00..179.33 rows=49 width=148)
441 Index Cond: (unique1 < 50)
442 -> Index Scan using tenk2_unique2 on tenk2 t2
443 (cost=0.00..3.01 rows=1 width=148)
444 Index Cond: ("outer".unique2 = t2.unique2)</PRE
448 > In this nested-loop join, the outer scan is the same index scan we had
449 in the example before last, and so its cost and row count are the same
450 because we are applying the <TT
459 >t1.unique2 = t2.unique2</TT
460 > clause is not relevant yet, so it doesn't
461 affect row count of the outer scan. For the inner scan, the <TT
466 outer-scan row is plugged into the inner index scan
467 to produce an index condition like
475 same inner-scan plan and costs that we'd get from, say, <TT
478 * FROM tenk2 WHERE unique2 = 42</TT
479 >. The costs of the loop node are then set
480 on the basis of the cost of the outer scan, plus one repetition of the
481 inner scan for each outer row (49 * 3.01, here), plus a little CPU
482 time for join processing.
485 > In this example the join's output row count is the same as the product
486 of the two scans' row counts, but that's not true in general, because
487 in general you can have <TT
490 > clauses that mention both tables and
491 so can only be applied at the join point, not to either input scan.
492 For example, if we added <TT
494 >WHERE ... AND t1.hundred < t2.hundred</TT
496 that would decrease the output row count of the join node, but not change
500 > One way to look at variant plans is to force the planner to disregard
501 whatever strategy it thought was the winner, using the enable/disable
502 flags for each plan type. (This is a crude tool, but useful. See
504 HREF="explicit-joins.html"
509 CLASS="PROGRAMLISTING"
510 >SET enable_nestloop = off;
511 EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
514 --------------------------------------------------------------------------
515 Hash Join (cost=179.45..563.06 rows=49 width=296)
516 Hash Cond: ("outer".unique2 = "inner".unique2)
517 -> Seq Scan on tenk2 t2 (cost=0.00..333.00 rows=10000 width=148)
518 -> Hash (cost=179.33..179.33 rows=49 width=148)
519 -> Index Scan using tenk1_unique1 on tenk1 t1
520 (cost=0.00..179.33 rows=49 width=148)
521 Index Cond: (unique1 < 50)</PRE
524 This plan proposes to extract the 50 interesting rows of <CODE
528 using ye same olde index scan, stash them into an in-memory hash table,
529 and then do a sequential scan of <CODE
532 >, probing into the hash table
533 for possible matches of <TT
535 >t1.unique2 = t2.unique2</TT
540 The cost to read <CODE
543 > and set up the hash table is entirely start-up
544 cost for the hash join, since we won't get any rows out until we can
548 >. The total time estimate for the join also
549 includes a hefty charge for the CPU time to probe the hash table
550 10000 times. Note, however, that we are <SPAN
556 > charging 10000 times 179.33;
557 the hash table setup is only done once in this plan type.
560 > It is possible to check on the accuracy of the planner's estimated costs
564 >. This command actually executes the query,
565 and then displays the true run time accumulated within each plan node
566 along with the same estimated costs that a plain <TT
570 For example, we might get a result like this:
574 >EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
577 -------------------------------------------------------------------------------
578 Nested Loop (cost=0.00..327.02 rows=49 width=296)
579 (actual time=1.181..29.822 rows=50 loops=1)
580 -> Index Scan using tenk1_unique1 on tenk1 t1
581 (cost=0.00..179.33 rows=49 width=148)
582 (actual time=0.630..8.917 rows=50 loops=1)
583 Index Cond: (unique1 < 50)
584 -> Index Scan using tenk2_unique2 on tenk2 t2
585 (cost=0.00..3.01 rows=1 width=148)
586 (actual time=0.295..0.324 rows=1 loops=50)
587 Index Cond: ("outer".unique2 = t2.unique2)
588 Total runtime: 31.604 ms</PRE
594 > values are in milliseconds of
595 real time, whereas the <SPAN
598 > estimates are expressed in
599 arbitrary units of disk fetches; so they are unlikely to match up.
600 The thing to pay attention to is the ratios.
603 > In some query plans, it is possible for a subplan node to be executed more
604 than once. For example, the inner index scan is executed once per outer
605 row in the above nested-loop plan. In such cases, the
610 total number of executions of the node, and the actual time and rows
611 values shown are averages per-execution. This is done to make the numbers
612 comparable with the way that the cost estimates are shown. Multiply by
616 > value to get the total time actually spent in
627 executor start-up and shut-down time, as well as time spent processing
628 the result rows. It does not include parsing, rewriting, or planning
632 > query, the total run time will normally be just a
633 little larger than the total time reported for the top-level plan node.
643 > commands, the total run time may be
644 considerably larger, because it includes the time spent processing the
645 result rows. In these commands, the time for the top plan node
646 essentially is the time spent computing the new rows and/or locating
647 the old ones, but it doesn't include the time spent making the changes.
650 > It is worth noting that <TT
653 > results should not be extrapolated
654 to situations other than the one you are actually testing; for example,
655 results on a toy-sized table can't be assumed to apply to large tables.
656 The planner's cost estimates are not linear and so it may well choose
657 a different plan for a larger or smaller table. An extreme example
658 is that on a table that only occupies one disk page, you'll nearly
659 always get a sequential scan plan whether indexes are available or not.
660 The planner realizes that it's going to take one disk page read to
661 process the table in any case, so there's no value in expending additional
662 page reads to look at an index.
671 SUMMARY="Footer navigation table"
682 HREF="locking-indexes.html"
700 HREF="planner-stats.html"
710 >Locking and Indexes</TD
724 >Statistics Used by the Planner</TD