On Fri, Jul 10, 2009 at 2:48 PM, Tom Lane<[email protected]> wrote:
> "Kevin Grittner" <[email protected]> writes:
>> You do, but it's been pretty rare in my experience, and we're
>> considering alternatives which give a lot less flexibility that this.
>
> I dunno about "considering". We've already wasted vastly more time on
> this than it's worth. AFAIR there has never been one single user
> request for the ability to partially constrain join order. I think we
> should do an enable_join_ordering boolean and quit wasting brainpower on
> the issue.
Patch attached.
...Robert
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 1798,1803 **** archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"' # Windows
--- 1798,1823 ----
</listitem>
</varlistentry>
+ <varlistentry id="guc-enable-join-ordering" xreflabel="enable_join_ordering">
+ <term><varname>enable_join_ordering</varname> (<type>boolean</type>)</term>
+ <indexterm>
+ <primary><varname>enable_join_ordering</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Allows the planner to reorder joins, which is generally appropriate
+ for most uses. Setting it to false prevents any reordering of
+ explicit <literal>JOIN</>s. 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 can elect to
+ temporarily set this variable to false, and then specify the join
+ order they desire explicitly.
+ For more information see <xref linkend="explicit-joins">.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
<term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
<indexterm>
***************
*** 2251,2313 **** SELECT * FROM parent WHERE key = 2400;
</listitem>
</varlistentry>
- <varlistentry id="guc-from-collapse-limit" xreflabel="from_collapse_limit">
- <term><varname>from_collapse_limit</varname> (<type>integer</type>)</term>
- <indexterm>
- <primary><varname>from_collapse_limit</> configuration parameter</primary>
- </indexterm>
- <listitem>
- <para>
- The planner will merge sub-queries into upper queries if the
- resulting <literal>FROM</literal> list would have no more than
- this many items. Smaller values reduce planning time but might
- yield inferior query plans. The default is eight.
- For more information see <xref linkend="explicit-joins">.
- </para>
-
- <para>
- Setting this value to <xref linkend="guc-geqo-threshold"> or more
- may trigger use of the GEQO planner, resulting in nondeterministic
- plans. See <xref linkend="runtime-config-query-geqo">.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-join-collapse-limit" xreflabel="join_collapse_limit">
- <term><varname>join_collapse_limit</varname> (<type>integer</type>)</term>
- <indexterm>
- <primary><varname>join_collapse_limit</> configuration parameter</primary>
- </indexterm>
- <listitem>
- <para>
- The planner will rewrite explicit <literal>JOIN</>
- constructs (except <literal>FULL JOIN</>s) into lists of
- <literal>FROM</> items whenever a list of no more than this many items
- would result. Smaller values reduce planning time but might
- yield inferior query plans.
- </para>
-
- <para>
- By default, this variable is set the same as
- <varname>from_collapse_limit</varname>, which is appropriate
- for most uses. Setting it to 1 prevents any reordering of
- explicit <literal>JOIN</>s. 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 can elect to
- temporarily set this variable to 1, and then specify the join
- order they desire explicitly.
- For more information see <xref linkend="explicit-joins">.
- </para>
-
- <para>
- Setting this value to <xref linkend="guc-geqo-threshold"> or more
- may trigger use of the GEQO planner, resulting in nondeterministic
- plans. See <xref linkend="runtime-config-query-geqo">.
- </para>
- </listitem>
- </varlistentry>
-
</variablelist>
</sect2>
</sect1>
--- 2271,2276 ----
*** a/doc/src/sgml/perform.sgml
--- b/doc/src/sgml/perform.sgml
***************
*** 692,699 **** SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
<para>
To force the planner to follow the join order laid out by explicit
<literal>JOIN</>s,
! set the <xref linkend="guc-join-collapse-limit"> run-time parameter to 1.
! (Other possible values are discussed below.)
</para>
<para>
--- 692,699 ----
<para>
To force the planner to follow the join order laid out by explicit
<literal>JOIN</>s,
! set the <xref linkend="guc-enable-join-ordering"> run-time parameter to
! false.
</para>
<para>
***************
*** 703,710 **** SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
<programlisting>
SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;
</programlisting>
! With <varname>join_collapse_limit</> = 1, this
! 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.
</para>
--- 703,709 ----
<programlisting>
SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;
</programlisting>
! This 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.
</para>
***************
*** 741,765 **** SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
we have increased the planning time; here, we have a five-way join
problem replacing two separate three-way join problems. Because of the
exponential growth of the number of possibilities, this makes a big
! difference. The planner tries to avoid getting stuck in huge join search
! problems by not collapsing a subquery if more than <varname>from_collapse_limit</>
! <literal>FROM</> items would result in the parent
! query. You can trade off planning time against quality of plan by
! adjusting this run-time parameter up or down.
! </para>
!
! <para>
! <xref linkend="guc-from-collapse-limit"> and <xref
! linkend="guc-join-collapse-limit">
! are similarly named because they do almost the same thing: one controls
! when the planner will <quote>flatten out</> subqueries, and the
! other controls when it will flatten out explicit joins. Typically
! you would either set <varname>join_collapse_limit</> equal to
! <varname>from_collapse_limit</> (so that explicit joins and subqueries
! act similarly) or set <varname>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
! time and run time.
</para>
</sect1>
--- 740,746 ----
we have increased the planning time; here, we have a five-way join
problem replacing two separate three-way join problems. Because of the
exponential growth of the number of possibilities, this makes a big
! difference.
</para>
</sect1>
*** a/src/backend/optimizer/README
--- b/src/backend/optimizer/README
***************
*** 89,106 **** single join relation.
2) Normally, any explicit JOIN clauses are "flattened" so that we just
have a list of relations to join. However, FULL OUTER JOIN clauses are
! never flattened, and other kinds of JOIN might not be either, if the
! flattening process is stopped by join_collapse_limit or from_collapse_limit
! restrictions. Therefore, we end up with a planning problem that contains
! lists of relations to be joined in any order, where any individual item
! might be a sub-list that has to be joined together before we can consider
! joining it to its siblings. We process these sub-problems recursively,
! bottom up. Note that the join list structure constrains the possible join
! orders, but it doesn't constrain the join implementation method at each
! join (nestloop, merge, hash), nor does it say which rel is considered outer
! or inner at each join. We consider all these possibilities in building
! Paths. We generate a Path for each feasible join method, and select the
! cheapest Path.
For each planning problem, therefore, we will have a list of relations
that are either base rels or joinrels constructed per sub-join-lists.
--- 89,105 ----
2) Normally, any explicit JOIN clauses are "flattened" so that we just
have a list of relations to join. However, FULL OUTER JOIN clauses are
! never flattened and other kinds of JOIN might not be either, if
! enable_join_ordering is set to false. Therefore, we end up with a planning
! problem that contains lists of relations to be joined in any order, where
! any individual item might be a sub-list that has to be joined together
! before we can consider joining it to its siblings. We process these
! sub-problems recursively, bottom up. Note that the join list structure
! constrains the possible join orders, but it doesn't constrain the join
! implementation method at each join (nestloop, merge, hash), nor does it
! say which rel is considered outer or inner at each join. We consider
! all these possibilities in building Paths. We generate a Path for each
! feasible join method, and select the cheapest Path.
For each planning problem, therefore, we will have a list of relations
that are either base rels or joinrels constructed per sub-join-lists.
*** a/src/backend/optimizer/plan/initsplan.c
--- b/src/backend/optimizer/plan/initsplan.c
***************
*** 33,41 ****
#include "utils/syscache.h"
! /* These parameters are set by GUC */
! int from_collapse_limit;
! int join_collapse_limit;
static List *deconstruct_recurse(PlannerInfo *root, Node *jtnode,
--- 33,40 ----
#include "utils/syscache.h"
! /* This parameter is set by GUC */
! bool enable_join_ordering;
static List *deconstruct_recurse(PlannerInfo *root, Node *jtnode,
***************
*** 214,221 **** add_vars_to_targetlist(PlannerInfo *root, List *vars, Relids where_needed)
* joinlist must be joined in an order to be determined by make_one_rel()
* (note that legal orders may be constrained by SpecialJoinInfo nodes).
* A sub-joinlist represents a subproblem to be planned separately. Currently
! * sub-joinlists arise only from FULL OUTER JOIN or when collapsing of
! * subproblems is stopped by join_collapse_limit or from_collapse_limit.
*
* NOTE: when dealing with inner joins, it is appropriate to let a qual clause
* be evaluated at the lowest level where all the variables it mentions are
--- 213,220 ----
* joinlist must be joined in an order to be determined by make_one_rel()
* (note that legal orders may be constrained by SpecialJoinInfo nodes).
* A sub-joinlist represents a subproblem to be planned separately. Currently
! * sub-joinlists arise only from FULL OUTER JOIN or when enable_join_ordering
! * is set to false.
*
* NOTE: when dealing with inner joins, it is appropriate to let a qual clause
* be evaluated at the lowest level where all the variables it mentions are
***************
*** 284,302 **** deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
else if (IsA(jtnode, FromExpr))
{
FromExpr *f = (FromExpr *) jtnode;
- int remaining;
ListCell *l;
/*
* First, recurse to handle child joins. We collapse subproblems into
! * a single joinlist whenever the resulting joinlist wouldn't exceed
! * from_collapse_limit members. Also, always collapse one-element
! * subproblems, since that won't lengthen the joinlist anyway.
*/
*qualscope = NULL;
*inner_join_rels = NULL;
joinlist = NIL;
- remaining = list_length(f->fromlist);
foreach(l, f->fromlist)
{
Relids sub_qualscope;
--- 283,297 ----
else if (IsA(jtnode, FromExpr))
{
FromExpr *f = (FromExpr *) jtnode;
ListCell *l;
/*
* First, recurse to handle child joins. We collapse subproblems into
! * a single joinlist.
*/
*qualscope = NULL;
*inner_join_rels = NULL;
joinlist = NIL;
foreach(l, f->fromlist)
{
Relids sub_qualscope;
***************
*** 309,320 **** deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
inner_join_rels);
*qualscope = bms_add_members(*qualscope, sub_qualscope);
sub_members = list_length(sub_joinlist);
! remaining--;
! if (sub_members <= 1 ||
! list_length(joinlist) + sub_members + remaining <= from_collapse_limit)
! joinlist = list_concat(joinlist, sub_joinlist);
! else
! joinlist = lappend(joinlist, sub_joinlist);
}
/*
--- 304,310 ----
inner_join_rels);
*qualscope = bms_add_members(*qualscope, sub_qualscope);
sub_members = list_length(sub_joinlist);
! joinlist = list_concat(joinlist, sub_joinlist);
}
/*
***************
*** 469,484 **** deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
/*
* Finally, compute the output joinlist. We fold subproblems together
! * except at a FULL JOIN or where join_collapse_limit would be
! * exceeded.
*/
if (j->jointype == JOIN_FULL)
{
/* force the join order exactly at this node */
joinlist = list_make1(list_make2(leftjoinlist, rightjoinlist));
}
! else if (list_length(leftjoinlist) + list_length(rightjoinlist) <=
! join_collapse_limit)
{
/* OK to combine subproblems */
joinlist = list_concat(leftjoinlist, rightjoinlist);
--- 459,472 ----
/*
* Finally, compute the output joinlist. We fold subproblems together
! * except at a FULL JOIN or when enable_join_ordering is false.
*/
if (j->jointype == JOIN_FULL)
{
/* force the join order exactly at this node */
joinlist = list_make1(list_make2(leftjoinlist, rightjoinlist));
}
! else if (enable_join_ordering)
{
/* OK to combine subproblems */
joinlist = list_concat(leftjoinlist, rightjoinlist);
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
***************
*** 656,661 **** static struct config_bool ConfigureNamesBool[] =
--- 656,669 ----
true, NULL, NULL
},
{
+ {"enable_join_ordering", PGC_USERSET, QUERY_TUNING_METHOD,
+ gettext_noop("Enables the planner to reorder explicit joins."),
+ NULL
+ },
+ &enable_join_ordering,
+ true, NULL, NULL
+ },
+ {
{"geqo", PGC_USERSET, QUERY_TUNING_GEQO,
gettext_noop("Enables genetic query optimization."),
gettext_noop("This algorithm attempts to do planning without "
***************
*** 1259,1286 **** static struct config_int ConfigureNamesInt[] =
100, 1, 10000, NULL, NULL
},
{
- {"from_collapse_limit", PGC_USERSET, QUERY_TUNING_OTHER,
- gettext_noop("Sets the FROM-list size beyond which subqueries "
- "are not collapsed."),
- gettext_noop("The planner will merge subqueries into upper "
- "queries if the resulting FROM list would have no more than "
- "this many items.")
- },
- &from_collapse_limit,
- 8, 1, INT_MAX, NULL, NULL
- },
- {
- {"join_collapse_limit", PGC_USERSET, QUERY_TUNING_OTHER,
- gettext_noop("Sets the FROM-list size beyond which JOIN "
- "constructs are not flattened."),
- gettext_noop("The planner will flatten explicit JOIN "
- "constructs into lists of FROM items whenever a "
- "list of no more than this many items would result.")
- },
- &join_collapse_limit,
- 8, 1, INT_MAX, NULL, NULL
- },
- {
{"geqo_threshold", PGC_USERSET, QUERY_TUNING_GEQO,
gettext_noop("Sets the threshold of FROM items beyond which GEQO is used."),
NULL
--- 1267,1272 ----
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***************
*** 190,195 ****
--- 190,196 ----
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
+ #enable_join_reordering = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
***************
*** 219,227 ****
#default_statistics_target = 100 # range 1-10000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
- #from_collapse_limit = 8
- #join_collapse_limit = 8 # 1 disables collapsing of explicit
- # JOIN clauses
#------------------------------------------------------------------------------
--- 220,225 ----
*** a/src/include/optimizer/planmain.h
--- b/src/include/optimizer/planmain.h
***************
*** 79,86 **** extern bool is_projection_capable_plan(Plan *plan);
/*
* prototypes for plan/initsplan.c
*/
! extern int from_collapse_limit;
! extern int join_collapse_limit;
extern void add_base_rels_to_query(PlannerInfo *root, Node *jtnode);
extern void build_base_rel_tlists(PlannerInfo *root, List *final_tlist);
--- 79,85 ----
/*
* prototypes for plan/initsplan.c
*/
! extern bool enable_join_ordering;
extern void add_base_rels_to_query(PlannerInfo *root, Node *jtnode);
extern void build_base_rel_tlists(PlannerInfo *root, List *final_tlist);
*** a/src/test/regress/expected/rangefuncs.out
--- b/src/test/regress/expected/rangefuncs.out
***************
*** 1,16 ****
SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%';
! name | setting
! -------------------+---------
! enable_bitmapscan | on
! enable_hashagg | on
! enable_hashjoin | on
! enable_indexscan | on
! enable_mergejoin | on
! enable_nestloop | on
! enable_seqscan | on
! enable_sort | on
! enable_tidscan | on
! (9 rows)
CREATE TABLE foo2(fooid int, f2 int);
INSERT INTO foo2 VALUES(1, 11);
--- 1,17 ----
SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%';
! name | setting
! ----------------------+---------
! enable_bitmapscan | on
! enable_hashagg | on
! enable_hashjoin | on
! enable_indexscan | on
! enable_join_ordering | on
! enable_mergejoin | on
! enable_nestloop | on
! enable_seqscan | on
! enable_sort | on
! enable_tidscan | on
! (10 rows)
CREATE TABLE foo2(fooid int, f2 int);
INSERT INTO foo2 VALUES(1, 11);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
