From ad4fd77a5f7708b28c130b7ce1d81b367fe8cc4c Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 16 Dec 2000 02:29:36 +0000 Subject: [PATCH] Restructure performance tips into a single chapter ('populating a database' was way too small to make a chapter). Add a section about using JOIN syntax to direct the planner. --- doc/src/sgml/filelist.sgml | 5 +- doc/src/sgml/perform.sgml | 440 +++++++++++++++++++++++++++++++++++++ doc/src/sgml/plan.sgml | 269 ----------------------- doc/src/sgml/populate.sgml | 79 ------- doc/src/sgml/user.sgml | 5 +- 5 files changed, 444 insertions(+), 354 deletions(-) create mode 100644 doc/src/sgml/perform.sgml delete mode 100644 doc/src/sgml/plan.sgml delete mode 100644 doc/src/sgml/populate.sgml diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 98b5014ce0..8458ae7275 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -1,4 +1,4 @@ - + @@ -26,11 +26,10 @@ - + - diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml new file mode 100644 index 0000000000..897073742b --- /dev/null +++ b/doc/src/sgml/perform.sgml @@ -0,0 +1,440 @@ + + + + Performance Tips + + + Query performance can be affected by many things. Some of these can + be manipulated by the user, while others are fundamental to the underlying + design of the system. This chapter provides some hints about understanding + and tuning Postgres performance. + + + + Using <command>EXPLAIN</command> + + + Author + + Written by Tom Lane, from e-mail dated 2000-03-27. + + + + + Postgres devises a query + plan for each query it is given. Choosing the right + plan to match the query structure and the properties of the data + is absolutely critical for good performance. You can use the + EXPLAIN command to see what query plan the system + creates for any query. Unfortunately, + plan-reading is an art that deserves a tutorial, and I haven't + had time to write one. Here is some quick & dirty explanation. + + + + The numbers that are currently quoted by EXPLAIN are: + + + + + Estimated start-up cost (time expended before output scan can start, + eg, time to do the sorting in a SORT node). + + + + + + Estimated total cost (if all tuples are retrieved, which they may not + be --- a query with a LIMIT will stop short of paying the total cost, + for example). + + + + + + Estimated number of rows output by this plan node (again, without + regard for any LIMIT). + + + + + + Estimated average width (in bytes) of rows output by this plan + node. + + + + + + + The costs are measured in units of disk page fetches. (CPU effort + estimates are converted into disk-page units using some + fairly arbitrary fudge-factors. If you want to experiment with these + factors, see the list of run-time configuration parameters in the + Administrator's Guide.) + + + + It's important to note that the cost of an upper-level node includes + the cost of all its child nodes. It's also important to realize that + the cost only reflects things that the planner/optimizer cares about. + In particular, the cost does not consider the time spent transmitting + result tuples to the frontend --- which could be a pretty dominant + factor in the true elapsed time, but the planner ignores it because + it cannot change it by altering the plan. (Every correct plan will + output the same tuple set, we trust.) + + + + Rows output is a little tricky because it is not the + number of rows + processed/scanned by the query --- it is usually less, reflecting the + estimated selectivity of any WHERE-clause constraints that are being + applied at this node. Ideally the top-level rows estimate will + approximate the number of rows actually returned, updated, or deleted + by the query (again, without considering the effects of LIMIT). + + + + Average width is pretty bogus because the thing really doesn't have + any idea of the average length of variable-length columns. I'm thinking + about improving that in the future, but it may not be worth the trouble, + because the width isn't used for very much. + + + + Here are some examples (using the regress test database after a + vacuum analyze, and almost-7.0 sources): + + +regression=# explain select * from tenk1; +NOTICE: QUERY PLAN: + +Seq Scan on tenk1 (cost=0.00..333.00 rows=10000 width=148) + + + + + This is about as straightforward as it gets. If you do + + +select * from pg_class where relname = 'tenk1'; + + + you'll find out that tenk1 has 233 disk + pages and 10000 tuples. So the cost is estimated at 233 block + reads, defined as 1.0 apiece, plus 10000 * cpu_tuple_cost which is + currently 0.01 (try show cpu_tuple_cost). + + + + Now let's modify the query to add a qualification clause: + + +regression=# explain select * from tenk1 where unique1 < 1000; +NOTICE: QUERY PLAN: + +Seq Scan on tenk1 (cost=0.00..358.00 rows=1000 width=148) + + + The estimate of output rows has gone down because of the WHERE clause. + (This estimate is uncannily accurate because tenk1 is a particularly + simple case --- the unique1 column has 10000 distinct values ranging + from 0 to 9999, so the estimator's linear interpolation between min and + max column values is dead-on.) However, the scan will still have to + visit all 10000 rows, so the cost hasn't decreased; in fact it has gone + up a bit to reflect the extra CPU time spent checking the WHERE + condition. + + + + Modify the query to restrict the qualification even more: + + +regression=# explain select * from tenk1 where unique1 < 100; +NOTICE: QUERY PLAN: + +Index Scan using tenk1_unique1 on tenk1 (cost=0.00..89.35 rows=100 width=148) + + + and you will see that if we make the WHERE condition selective + enough, the planner will + eventually decide that an indexscan is cheaper than a sequential scan. + This plan will only have to visit 100 tuples because of the index, + so it wins despite the fact that each individual fetch is expensive. + + + + Add another condition to the qualification: + + +regression=# explain select * from tenk1 where unique1 < 100 and +regression-# stringu1 = 'xxx'; +NOTICE: QUERY PLAN: + +Index Scan using tenk1_unique1 on tenk1 (cost=0.00..89.60 rows=1 width=148) + + + The added clause "stringu1 = 'xxx'" reduces the output-rows estimate, + but not the cost because we still have to visit the same set of tuples. + + + + Let's try joining two tables, using the fields we have been discussing: + + +regression=# explain select * from tenk1 t1, tenk2 t2 where t1.unique1 < 100 +regression-# and t1.unique2 = t2.unique2; +NOTICE: QUERY PLAN: + +Nested Loop (cost=0.00..144.07 rows=100 width=296) + -> Index Scan using tenk1_unique1 on tenk1 t1 + (cost=0.00..89.35 rows=100 width=148) + -> Index Scan using tenk2_unique2 on tenk2 t2 + (cost=0.00..0.53 rows=1 width=148) + + + + + In this nested-loop join, the outer scan is the same indexscan we had + in the example before last, and so its cost and row count are the same + because we are applying the "unique1 < 100" WHERE clause at that node. + The "t1.unique2 = t2.unique2" clause isn't relevant yet, so it doesn't + affect the outer scan's row count. For the inner scan, the + current + outer-scan tuple's unique2 value is plugged into the inner indexscan + to produce an indexqual like + "t2.unique2 = constant". So we get the + same inner-scan plan and costs that we'd get from, say, "explain select + * from tenk2 where unique2 = 42". The loop node's costs are then set + on the basis of the outer scan's cost, plus one repetition of the + inner scan for each outer tuple (100 * 0.53, here), plus a little CPU + time for join processing. + + + + In this example the loop's output row count is the same as the product + of the two scans' row counts, but that's not true in general, because + in general you can have WHERE clauses that mention both relations and + so can only be applied at the join point, not to either input scan. + For example, if we added "WHERE ... AND t1.hundred < t2.hundred", + that'd decrease the output row count of the join node, but not change + either input scan. + + + + One way to look at variant plans is to force the planner to disregard + whatever strategy it thought was the winner, using the enable/disable + flags for each plan type. (This is a crude tool, but useful. See + also .) + + +regression=# set enable_nestloop = off; +SET VARIABLE +regression=# explain select * from tenk1 t1, tenk2 t2 where t1.unique1 < 100 +regression-# and t1.unique2 = t2.unique2; +NOTICE: QUERY PLAN: + +Hash Join (cost=89.60..574.10 rows=100 width=296) + -> Seq Scan on tenk2 t2 + (cost=0.00..333.00 rows=10000 width=148) + -> Hash (cost=89.35..89.35 rows=100 width=148) + -> Index Scan using tenk1_unique1 on tenk1 t1 + (cost=0.00..89.35 rows=100 width=148) + + + This plan proposes to extract the 100 interesting rows of tenk1 + using ye same olde indexscan, stash them into an in-memory hash table, + and then do a sequential scan of tenk2, probing into the hash table + for possible matches of "t1.unique2 = t2.unique2" at each tenk2 tuple. + The cost to read tenk1 and set up the hash table is entirely start-up + cost for the hash join, since we won't get any tuples out until we can + start reading tenk2. The total time estimate for the join also + includes a pretty hefty charge for CPU time to probe the hash table + 10000 times. Note, however, that we are NOT charging 10000 times 89.35; + the hash table setup is only done once in this plan type. + + + + + Controlling the Planner with Explicit JOINs + + + Beginning with Postgres 7.1 it is possible + to control the query planner to some extent by using explicit JOIN + syntax. To see why this matters, we first need some background. + + + + In a simple join query, such as + +SELECT * FROM a,b,c WHERE a.id = b.id AND b.ref = c.id; + + the planner is free to join the given tables in any order. For example, + it could generate a query plan that joins A to B, using the WHERE clause + a.id = b.id, and then joins C to this joined table, using the other + WHERE clause. Or it could join B to C and then join A to that result. + Or it could join A to C and then join them with B --- but that would + be inefficient, since the full Cartesian product of A and C would have + to be formed, there being no applicable WHERE clause to allow optimization + of the join. + (All joins in the Postgres executor happen + between two input tables, so it's necessary to build up the result in one + or another of these fashions.) The important point is that these different + join possibilities give semantically equivalent results but may have hugely + different execution costs. Therefore, the planner will explore all of them + to try to find the most efficient query plan. + + + + When a query only involves two or three tables, there aren't many join + orders to worry about. But the number of possible join orders grows + exponentially as the number of tables expands. Beyond ten or so input + tables it's no longer practical to do an exhaustive search of all the + possibilities, and even for six or seven tables planning may take an + annoyingly long time. When there are too many input tables, the + Postgres planner will switch from exhaustive + search to a genetic probabilistic search + through a limited number of possibilities. (The switchover threshold is + set by the GEQO_THRESHOLD run-time + parameter described in the Administrator's Guide.) + The genetic search takes less time, but it won't + necessarily find the best possible plan. + + + + When the query involves outer joins, the planner has much less freedom + than it does for plain (inner) joins. For example, consider + +SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id); + + Although this query's restrictions are superficially similar to the + previous example, the semantics are different because a row must be + emitted for each row of A that has no matching row in the join of B and C. + Therefore the planner has no choice of join order here: it must join + B to C and then join A to that result. Accordingly, this query takes + less time to plan than the previous query. + + + + In Postgres 7.1, the planner treats all + explicit JOIN syntaxes as constraining the join order, even though + it is not logically necessary to make such a constraint for inner + joins. Therefore, although all of these queries give the same result: + +SELECT * FROM a,b,c WHERE a.id = b.id AND b.ref = c.id; +SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id; +SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id); + + the second and third take less time to plan than the first. This effect + is not worth worrying about for only three tables, but it can be a + lifesaver with many tables. + + + + You do not need to constrain the join order completely in order to + cut search time, because it's OK to use JOIN operators in a plain + FROM list. For example, + +SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...; + + forces the planner to join A to B before joining them to other tables, + but doesn't constrain its choices otherwise. In this example, the + number of possible join orders is reduced by a factor of 5. + + + + If you have a mix of outer and inner joins in a complex query, you + might not want to constrain the planner's search for a good ordering + of inner joins inside an outer join. You can't do that directly in the + JOIN syntax, but you can get around the syntactic limitation by using + views. For example, + +CREATE VIEW v1 AS SELECT ... FROM a, b, c WHERE ...; +SELECT * FROM d LEFT JOIN v1 ON (...); + + Here, joining D must be the last step in the query plan, but the + planner is free to consider various join orders for A,B,C. + + + + Constraining the planner's search in this way is a useful technique + both for reducing planning time and for directing the planner to a + good query plan. If the planner chooses a bad join order by default, + you can force it to choose a better order via JOIN syntax --- assuming + that you know of a better order, that is. Experimentation is recommended. + + + + + Populating a Database + + + One may need to do a large number of table insertions when first + populating a database. Here are some tips and techniques for making that as + efficient as possible. + + + + Disable Auto-commit + + + Turn off auto-commit and just do one commit at + the end. Otherwise Postgres is doing a + lot of work for each record + added. In general when you are doing bulk inserts, you want + to turn off some of the database features to gain speed. + + + + + Use COPY FROM + + + Use COPY FROM STDIN to load all the records in one + command, instead + of a series of INSERT commands. This reduces parsing, planning, etc + overhead a great deal. If you do this then it's not necessary to fool + around with autocommit. + + + + + Remove Indices + + + If you are loading a freshly created table, the fastest way is to + create the table, bulk-load with COPY, then create any indexes needed + for the table. Creating an index on pre-existing data is quicker than + updating it incrementally as each record is loaded. + + + + If you are augmenting an existing table, you can DROP + INDEX, load the table, then recreate the index. Of + course, the database performance for other users may be adversely + affected during the time that the index is missing. + + + + + + + diff --git a/doc/src/sgml/plan.sgml b/doc/src/sgml/plan.sgml deleted file mode 100644 index bfc592b6a4..0000000000 --- a/doc/src/sgml/plan.sgml +++ /dev/null @@ -1,269 +0,0 @@ - - - - Understanding Performance - - - Query performance can be affected by many things. Some of these can - be manipulated by the user, while others are fundamental to the underlying - design of the system. - - - - Some performance issues, such as index creation and bulk data - loading, are covered elsewhere. This chapter will discuss the - EXPLAIN command, and will show how the details - of a query can affect the query plan, and hence overall - performance. - - - - Using <command>EXPLAIN</command> - - - Author - - Written by Tom Lane, from e-mail dated 2000-03-27. - - - - - Plan-reading is an art that deserves a tutorial, and I haven't - had time to write one. Here is some quick & dirty explanation. - - - - The numbers that are currently quoted by EXPLAIN are: - - - - - Estimated start-up cost (time expended before output scan can start, - eg, time to do the sorting in a SORT node). - - - - - - Estimated total cost (if all tuples are retrieved, which they may not - be --- LIMIT will stop short of paying the total cost, for - example). - - - - - - Estimated number of rows output by this plan node. - - - - - - Estimated average width (in bytes) of rows output by this plan - node. - - - - - - - The costs are measured in units of disk page fetches. (CPU effort - estimates are converted into disk-page units using some - fairly arbitrary fudge-factors. See the SET - reference page if you want to experiment with these.) - It's important to note that the cost of an upper-level node includes - the cost of all its child nodes. It's also important to realize that - the cost only reflects things that the planner/optimizer cares about. - In particular, the cost does not consider the time spent transmitting - result tuples to the frontend --- which could be a pretty dominant - factor in the true elapsed time, but the planner ignores it because - it cannot change it by altering the plan. (Every correct plan will - output the same tuple set, we trust.) - - - - Rows output is a little tricky because it is not the number of rows - processed/scanned by the query --- it is usually less, reflecting the - estimated selectivity of any WHERE-clause constraints that are being - applied at this node. - - - - Average width is pretty bogus because the thing really doesn't have - any idea of the average length of variable-length columns. I'm thinking - about improving that in the future, but it may not be worth the trouble, - because the width isn't used for very much. - - - - Here are some examples (using the regress test database after a - vacuum analyze, and almost-7.0 sources): - - -regression=# explain select * from tenk1; -NOTICE: QUERY PLAN: - -Seq Scan on tenk1 (cost=0.00..333.00 rows=10000 width=148) - - - - - This is about as straightforward as it gets. If you do - - -select * from pg_class where relname = 'tenk1'; - - - you'll find out that tenk1 has 233 disk - pages and 10000 tuples. So the cost is estimated at 233 block - reads, defined as 1.0 apiece, plus 10000 * cpu_tuple_cost which is - currently 0.01 (try show cpu_tuple_cost). - - - - Now let's modify the query to add a qualification clause: - - -regression=# explain select * from tenk1 where unique1 < 1000; -NOTICE: QUERY PLAN: - -Seq Scan on tenk1 (cost=0.00..358.00 rows=1000 width=148) - - - The estimate of output rows has gone down because of the WHERE clause. - (The uncannily accurate estimate is just because tenk1 is a particularly - simple case --- the unique1 column has 10000 distinct values ranging - from 0 to 9999, so the estimator's linear interpolation between min and - max column values is dead-on.) However, the scan will still have to - visit all 10000 rows, so the cost hasn't decreased; in fact it has gone - up a bit to reflect the extra CPU time spent checking the WHERE - condition. - - - - Modify the query to restrict the qualification even more: - - -regression=# explain select * from tenk1 where unique1 < 100; -NOTICE: QUERY PLAN: - -Index Scan using tenk1_unique1 on tenk1 (cost=0.00..89.35 rows=100 width=148) - - - and you will see that if we make the WHERE condition selective - enough, the planner will - eventually decide that an indexscan is cheaper than a sequential scan. - This plan will only have to visit 100 tuples because of the index, - so it wins despite the fact that each individual fetch is expensive. - - - - Add another condition to the qualification: - - -regression=# explain select * from tenk1 where unique1 < 100 and -regression-# stringu1 = 'xxx'; -NOTICE: QUERY PLAN: - -Index Scan using tenk1_unique1 on tenk1 (cost=0.00..89.60 rows=1 width=148) - - - The added clause "stringu1 = 'xxx'" reduces the output-rows estimate, - but not the cost because we still have to visit the same set of tuples. - - - - Let's try joining two tables, using the fields we have been discussing: - - -regression=# explain select * from tenk1 t1, tenk2 t2 where t1.unique1 < 100 -regression-# and t1.unique2 = t2.unique2; -NOTICE: QUERY PLAN: - -Nested Loop (cost=0.00..144.07 rows=100 width=296) - -> Index Scan using tenk1_unique1 on tenk1 t1 - (cost=0.00..89.35 rows=100 width=148) - -> Index Scan using tenk2_unique2 on tenk2 t2 - (cost=0.00..0.53 rows=1 width=148) - - - - - In this nested-loop join, the outer scan is the same indexscan we had - in the example before last, and so its cost and row count are the same - because we are applying the "unique1 < 100" WHERE clause at that node. - The "t1.unique2 = t2.unique2" clause isn't relevant yet, so it doesn't - affect the outer scan's row count. For the inner scan, the - current - outer-scan tuple's unique2 value is plugged into the inner indexscan - to produce an indexqual like - "t2.unique2 = constant". So we get the - same inner-scan plan and costs that we'd get from, say, "explain select - * from tenk2 where unique2 = 42". The loop node's costs are then set - on the basis of the outer scan's cost, plus one repetition of the - inner scan for each outer tuple (100 * 0.53, here), plus a little CPU - time for join processing. - - - - In this example the loop's output row count is the same as the product - of the two scans' row counts, but that's not true in general, because - in general you can have WHERE clauses that mention both relations and - so can only be applied at the join point, not to either input scan. - For example, if we added "WHERE ... AND t1.hundred < t2.hundred", - that'd decrease the output row count of the join node, but not change - either input scan. - - - - We can look at variant plans by forcing the planner to disregard - whatever strategy it thought was the winner (a pretty crude tool, - but it's what we've got at the moment): - - -regression=# set enable_nestloop = off; -SET VARIABLE -regression=# explain select * from tenk1 t1, tenk2 t2 where t1.unique1 < 100 -regression-# and t1.unique2 = t2.unique2; -NOTICE: QUERY PLAN: - -Hash Join (cost=89.60..574.10 rows=100 width=296) - -> Seq Scan on tenk2 t2 - (cost=0.00..333.00 rows=10000 width=148) - -> Hash (cost=89.35..89.35 rows=100 width=148) - -> Index Scan using tenk1_unique1 on tenk1 t1 - (cost=0.00..89.35 rows=100 width=148) - - - This plan proposes to extract the 100 interesting rows of tenk1 - using ye same olde indexscan, stash them into an in-memory hash table, - and then do a sequential scan of tenk2, probing into the hash table - for possible matches of "t1.unique2 = t2.unique2" at each tenk2 tuple. - The cost to read tenk1 and set up the hash table is entirely start-up - cost for the hash join, since we won't get any tuples out until we can - start reading tenk2. The total time estimate for the join also - includes a pretty hefty charge for CPU time to probe the hash table - 10000 times. Note, however, that we are NOT charging 10000 times 89.35; - the hash table setup is only done once in this plan type. - - - - - diff --git a/doc/src/sgml/populate.sgml b/doc/src/sgml/populate.sgml deleted file mode 100644 index abac068e62..0000000000 --- a/doc/src/sgml/populate.sgml +++ /dev/null @@ -1,79 +0,0 @@ - - - - Populating a Database - - - Author - - Written by Tom Lane, from an e-mail message dated 1999-12-05. - - - - - One may need to do a large number of table insertions when first - populating a database. Here are some tips and techniques for making that as - efficient as possible. - - - - Disable Auto-commit - - - Turn off auto-commit and just do one commit at - the end. Otherwise Postgres is doing a - lot of work for each record - added. In general when you are doing bulk inserts, you want - to turn off some of the database features to gain speed. - - - - - Use COPY FROM - - - Use COPY FROM STDIN to load all the records in one - command, instead - of a series of INSERT commands. This reduces parsing, planning, etc - overhead a great deal. If you do this then it's not necessary to fool - around with autocommit. - - - - - Remove Indices - - - If you are loading a freshly created table, the fastest way is to - create the table, bulk-load with COPY, then create any indexes needed - for the table. Creating an index on pre-existing data is quicker than - updating it incrementally as each record is loaded. - - - - If you are augmenting an existing table, you can DROP - INDEX, load the table, then recreate the index. Of - course, the database performance for other users may be adversely - affected during the time that the index is missing. - - - - - diff --git a/doc/src/sgml/user.sgml b/doc/src/sgml/user.sgml index 64922acc39..e2f7874483 100644 --- a/doc/src/sgml/user.sgml +++ b/doc/src/sgml/user.sgml @@ -1,5 +1,5 @@ @@ -57,8 +57,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/Attic/user.sgml,v 1.21 2000/12/14 22:30:56 &environ; &manage; &storage; - &plan; - &populate + &perform; -- 2.40.0