From e3b9852728902bc816bf02574a87eda9a0ca91a1 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 20 Dec 2005 02:30:36 +0000 Subject: Teach planner how to rearrange join order for some classes of OUTER JOIN. Per my recent proposal. I ended up basing the implementation on the existing mechanism for enforcing valid join orders of IN joins --- the rules for valid outer-join orders are somewhat similar. --- doc/src/sgml/config.sgml | 43 ++++++++++--------------------------------- doc/src/sgml/perform.sgml | 41 ++++++++++++++++++++++++++++------------- 2 files changed, 38 insertions(+), 46 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 2d1e5081834..8322463cea7 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,5 +1,5 @@ Server Configuration @@ -2028,6 +2028,7 @@ SELECT * FROM parent WHERE key = 2400; this many items. Smaller values reduce planning time but may yield inferior query plans. The default is 8. It is usually wise to keep this less than . + For more information see . @@ -2039,48 +2040,24 @@ SELECT * FROM parent WHERE key = 2400; - The planner will rewrite explicit inner JOIN - constructs into lists of FROM items whenever a - list of no more than this many items in total would - result. Prior to PostgreSQL 7.4, joins - specified via the JOIN construct would - never be reordered by the query planner. The query planner has - subsequently been improved so that inner joins written in this - form can be reordered; this configuration parameter controls - the extent to which this reordering is performed. - - - At present, the order of outer joins specified via the - JOIN construct is never adjusted by the query - planner; therefore, join_collapse_limit has no - effect on this behavior. The planner may be improved to - reorder some classes of outer joins in a future release of - PostgreSQL. - - + The planner will rewrite explicit JOIN + constructs (except FULL JOINs) into lists of + FROM items whenever a list of no more than this many items + would result. Smaller values reduce planning time but may + yield inferior query plans. By default, this variable is set the same as from_collapse_limit, which is appropriate for most uses. Setting it to 1 prevents any reordering of - inner JOINs. Thus, the explicit join order + explicit JOINs. Thus, the explicit join order specified in the query will be the actual order in which the relations are joined. The query planner does not always choose the optimal join order; advanced users may elect to temporarily set this variable to 1, and then specify the join - order they desire explicitly. Another consequence of setting - this variable to 1 is that the query planner will behave more - like the PostgreSQL 7.3 query - planner, which some users might find useful for backward - compatibility reasons. - - - - Setting this variable to a value between 1 and - from_collapse_limit might be useful to - trade off planning time against the quality of the chosen plan - (higher values produce better plans). + order they desire explicitly. + For more information see . diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index 53fa8210f83..9632fc9a496 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -1,5 +1,5 @@ @@ -627,7 +627,7 @@ SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id; - When the query involves outer joins, the planner has much less freedom + When the query involves outer joins, the planner has 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); @@ -637,16 +637,30 @@ SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id); 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. + less time to plan than the previous query. In other cases, the planner + may be able to determine that more than one join order is safe. + For example, given + +SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id); + + it is valid to join A to either B or C first. Currently, only + FULL JOIN completely constrains the join order. Most + practical cases involving LEFT JOIN or RIGHT JOIN + can be rearranged to some extent. Explicit inner join syntax (INNER JOIN, CROSS JOIN, or unadorned JOIN) is semantically the same as - listing the input relations in FROM, so it does not need to - constrain the join order. But it is possible to instruct the - PostgreSQL query planner to treat - explicit inner JOINs as constraining the join order anyway. + listing the input relations in FROM, so it does not + constrain the join order. + + + + Even though most kinds of JOIN don't completely constrain + the join order, it is possible to instruct the + PostgreSQL query planner to treat all + JOIN clauses as constraining the join order anyway. For example, these three queries are logically equivalent: SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id; @@ -660,7 +674,8 @@ SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id); - To force the planner to follow the JOIN order for inner joins, + To force the planner to follow the join order laid out by explicit + JOINs, set the run-time parameter to 1. (Other possible values are discussed below.) @@ -697,9 +712,9 @@ FROM x, y, WHERE somethingelse; This situation might arise from use of a view that contains a join; - the view's SELECT rule will be inserted in place of the view reference, - yielding a query much like the above. Normally, the planner will try - to collapse the subquery into the parent, yielding + the view's SELECT rule will be inserted in place of the view + reference, yielding a query much like the above. Normally, the planner + will try to collapse the subquery into the parent, yielding SELECT * FROM x, y, a, b, c WHERE something AND somethingelse; @@ -722,12 +737,12 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse; linkend="guc-join-collapse-limit"> are similarly named because they do almost the same thing: one controls when the planner will flatten out subselects, and the - other controls when it will flatten out explicit inner joins. Typically + other controls when it will flatten out explicit joins. Typically you would either set join_collapse_limit equal to from_collapse_limit (so that explicit joins and subselects act similarly) or set join_collapse_limit to 1 (if you want to control join order with explicit joins). But you might set them - differently if you are trying to fine-tune the trade off between planning + differently if you are trying to fine-tune the trade-off between planning time and run time. -- cgit v1.2.3