On Wed, Jul 8, 2009 at 4:57 PM, Tom Lane<t...@sss.pgh.pa.us> wrote:
> Well, the reason I'm not voting for #3 is that it looks like a lot of
> work to implement something that would basically be a planner hint,
> which I'm generally against; furthermore, it's a hint that there's been
> no demand for. (We're not even certain that anyone is using the ability
> to *fully* specify the join order, much less wanting some undetermined
> compromise between manual and automatic control.) And anyway I didn't
> hear anyone volunteering to do it. So the realistic alternatives are
> #1, #2, or "do nothing"; and out of those I like #2.
I took a look at this and it seems that #3 can be implemented with
essentially no additional code (the handful of lines I added where
more than balanced out by some simplifications in ruleutils.c). Of
course you still don't have to like it. :-)
Patch attached.
...Robert
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 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>
--- 2251,2256 ----
*** a/doc/src/sgml/perform.sgml
--- b/doc/src/sgml/perform.sgml
***************
*** 599,606 **** WHERE tablename = 'road';
<para>
It is possible
! to control the query planner to some extent by using the explicit <literal>JOIN</>
! syntax. To see why this matters, we first need some background.
</para>
<para>
--- 599,607 ----
<para>
It is possible
! to control the query planner to some extent by using <literal>JOIN</>
! with the <literal>FORCE</> keyword. To see why this matters, we first need
! some background.
</para>
<para>
***************
*** 675,681 **** SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);
<para>
Even though most kinds of <literal>JOIN</> don't completely constrain
the join order, it is possible to instruct the
! <productname>PostgreSQL</productname> query planner to treat all
<literal>JOIN</> clauses as constraining the join order anyway.
For example, these three queries are logically equivalent:
<programlisting>
--- 676,682 ----
<para>
Even though most kinds of <literal>JOIN</> don't completely constrain
the join order, it is possible to instruct the
! <productname>PostgreSQL</productname> query planner to treat certain
<literal>JOIN</> clauses as constraining the join order anyway.
For example, these three queries are logically equivalent:
<programlisting>
***************
*** 683,710 **** 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);
</programlisting>
- But if we tell the planner to honor the <literal>JOIN</> order,
- 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.
</para>
<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>
You do not need to constrain the join order completely in order to
! cut search time, because it's OK to use <literal>JOIN</> operators
! within items of a plain <literal>FROM</> list. For example, consider:
<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>
--- 684,710 ----
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);
</programlisting>
</para>
<para>
To force the planner to follow the join order laid out by explicit
! <literal>JOIN</>s, use the <literal>FORCE</> keyword, like this:
! <programlisting>
! SELECT * FROM a INNER FORCE JOIN (b INNER FORCE JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
! </programlisting>
! Because there is only one possible join order, this query will take less
! time to plan. This effect is not worth worrying about for only three
! tables, but it can be a lifesaver with many tables.
</para>
<para>
You do not need to constrain the join order completely in order to
! cut search time; <literal>FORCE</> can be specified for just some of
! the joins in a given query. For example, consider:
<programlisting>
! SELECT * FROM a INNER FORCE JOIN b ON ..., 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>
***************
*** 713,719 **** SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;
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 <literal>JOIN</> syntax
— assuming that you know of a better order, that is. Experimentation
is recommended.
</para>
--- 713,719 ----
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 using <literal>FORCE</>
— assuming that you know of a better order, that is. Experimentation
is recommended.
</para>
***************
*** 729,736 **** WHERE somethingelse;
</programlisting>
This situation might arise from use of a view that contains a join;
the view's <literal>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:
<programlisting>
SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
</programlisting>
--- 729,736 ----
</programlisting>
This situation might arise from use of a view that contains a join;
the view's <literal>SELECT</> rule will be inserted in place of the view
! reference, yielding a query much like the above. The planner
! will always try to collapse the subquery into the parent, yielding:
<programlisting>
SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
</programlisting>
***************
*** 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>
--- 741,748 ----
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. For large join problems, you may need to constrain the join
! order or use <xref linkend="guc-geqo-threshold">.
</para>
</sect1>
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
***************
*** 357,372 **** TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] |
One of
<itemizedlist>
<listitem>
! <para><literal>[ INNER ] JOIN</literal></para>
</listitem>
<listitem>
! <para><literal>LEFT [ OUTER ] JOIN</literal></para>
</listitem>
<listitem>
! <para><literal>RIGHT [ OUTER ] JOIN</literal></para>
</listitem>
<listitem>
! <para><literal>FULL [ OUTER ] JOIN</literal></para>
</listitem>
<listitem>
<para><literal>CROSS JOIN</literal></para>
--- 357,372 ----
One of
<itemizedlist>
<listitem>
! <para><literal>[ INNER [ FORCE ] ] JOIN</literal></para>
</listitem>
<listitem>
! <para><literal>LEFT [ OUTER ] [ FORCE ] JOIN</literal></para>
</listitem>
<listitem>
! <para><literal>RIGHT [ OUTER ] [ FORCE ] JOIN</literal></para>
</listitem>
<listitem>
! <para><literal>FULL [ OUTER ] [ FORCE ] JOIN</literal></para>
</listitem>
<listitem>
<para><literal>CROSS JOIN</literal></para>
***************
*** 389,395 **** TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] |
determine the order of nesting. In the absence of parentheses,
<literal>JOIN</literal>s nest left-to-right. In any case
<literal>JOIN</literal> binds more tightly than the commas
! separating <literal>FROM</> items.
</para>
<para>
--- 389,397 ----
determine the order of nesting. In the absence of parentheses,
<literal>JOIN</literal>s nest left-to-right. In any case
<literal>JOIN</literal> binds more tightly than the commas
! separating <literal>FROM</> items. The keyword
! <literal>FORCE</literal> constrains the join order
! (see <xref linkend="explicit-joins">).
</para>
<para>
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 1542,1547 **** _copyJoinExpr(JoinExpr *from)
--- 1542,1548 ----
COPY_SCALAR_FIELD(jointype);
COPY_SCALAR_FIELD(isNatural);
+ COPY_SCALAR_FIELD(isForce);
COPY_NODE_FIELD(larg);
COPY_NODE_FIELD(rarg);
COPY_NODE_FIELD(using);
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 702,707 **** _equalJoinExpr(JoinExpr *a, JoinExpr *b)
--- 702,708 ----
{
COMPARE_SCALAR_FIELD(jointype);
COMPARE_SCALAR_FIELD(isNatural);
+ COMPARE_SCALAR_FIELD(isForce);
COMPARE_NODE_FIELD(larg);
COMPARE_NODE_FIELD(rarg);
COMPARE_NODE_FIELD(using);
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 1257,1262 **** _outJoinExpr(StringInfo str, JoinExpr *node)
--- 1257,1263 ----
WRITE_ENUM_FIELD(jointype, JoinType);
WRITE_BOOL_FIELD(isNatural);
+ WRITE_BOOL_FIELD(isForce);
WRITE_NODE_FIELD(larg);
WRITE_NODE_FIELD(rarg);
WRITE_NODE_FIELD(using);
*** a/src/backend/nodes/readfuncs.c
--- b/src/backend/nodes/readfuncs.c
***************
*** 1068,1073 **** _readJoinExpr(void)
--- 1068,1074 ----
READ_ENUM_FIELD(jointype, JoinType);
READ_BOOL_FIELD(isNatural);
+ READ_BOOL_FIELD(isForce);
READ_NODE_FIELD(larg);
READ_NODE_FIELD(rarg);
READ_NODE_FIELD(using);
*** a/src/backend/optimizer/README
--- b/src/backend/optimizer/README
***************
*** 90,99 **** 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
--- 90,98 ----
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
! FORCE keyword is used. 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
*** a/src/backend/optimizer/plan/initsplan.c
--- b/src/backend/optimizer/plan/initsplan.c
***************
*** 32,43 ****
#include "utils/lsyscache.h"
#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,
bool below_outer_join,
Relids *qualscope, Relids *inner_join_rels);
--- 32,37 ----
***************
*** 215,221 **** add_vars_to_targetlist(PlannerInfo *root, List *vars, Relids where_needed)
* (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
--- 209,215 ----
* (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 the FORCE keyword.
*
* 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,320 **** 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;
List *sub_joinlist;
- int sub_members;
sub_joinlist = deconstruct_recurse(root, lfirst(l),
below_outer_join,
&sub_qualscope,
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);
}
/*
--- 278,303 ----
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;
List *sub_joinlist;
sub_joinlist = deconstruct_recurse(root, lfirst(l),
below_outer_join,
&sub_qualscope,
inner_join_rels);
*qualscope = bms_add_members(*qualscope, sub_qualscope);
! joinlist = list_concat(joinlist, sub_joinlist);
}
/*
***************
*** 469,505 **** 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);
}
- else
- {
- /* can't combine, but needn't force join order above here */
- Node *leftpart,
- *rightpart;
-
- /* avoid creating useless 1-element sublists */
- if (list_length(leftjoinlist) == 1)
- leftpart = (Node *) linitial(leftjoinlist);
- else
- leftpart = (Node *) leftjoinlist;
- if (list_length(rightjoinlist) == 1)
- rightpart = (Node *) linitial(rightjoinlist);
- else
- rightpart = (Node *) rightjoinlist;
- joinlist = list_make2(leftpart, rightpart);
- }
}
else
{
--- 452,469 ----
/*
* Finally, compute the output joinlist. We fold subproblems together
! * except at a FULL JOIN or where FORCE has been specified.
*/
! if (j->jointype == JOIN_FULL || j->isForce)
{
/* force the join order exactly at this node */
joinlist = list_make1(list_make2(leftjoinlist, rightjoinlist));
}
! else
{
/* OK to combine subproblems */
joinlist = list_concat(leftjoinlist, rightjoinlist);
}
}
else
{
*** a/src/backend/optimizer/plan/subselect.c
--- b/src/backend/optimizer/plan/subselect.c
***************
*** 1087,1092 **** convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink,
--- 1087,1093 ----
result = makeNode(JoinExpr);
result->jointype = JOIN_SEMI;
result->isNatural = false;
+ result->isForce = false;
result->larg = NULL; /* caller must fill this in */
result->rarg = (Node *) rtr;
result->using = NIL;
***************
*** 1227,1232 **** convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
--- 1228,1234 ----
result = makeNode(JoinExpr);
result->jointype = under_not ? JOIN_ANTI : JOIN_SEMI;
result->isNatural = false;
+ result->isForce = false;
result->larg = NULL; /* caller must fill this in */
/* flatten out the FromExpr node if it's useless */
if (list_length(subselect->jointree->fromlist) == 1)
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 7437,7459 **** joined_table:
JoinExpr *n = makeNode(JoinExpr);
n->jointype = JOIN_INNER;
n->isNatural = FALSE;
n->larg = $1;
n->rarg = $4;
n->using = NIL;
n->quals = NULL;
$$ = n;
}
! | table_ref join_type JOIN table_ref join_qual
{
JoinExpr *n = makeNode(JoinExpr);
n->jointype = $2;
n->isNatural = FALSE;
n->larg = $1;
! n->rarg = $4;
! if ($5 != NULL && IsA($5, List))
! n->using = (List *) $5; /* USING clause */
else
! n->quals = $5; /* ON clause */
$$ = n;
}
| table_ref JOIN table_ref join_qual
--- 7437,7461 ----
JoinExpr *n = makeNode(JoinExpr);
n->jointype = JOIN_INNER;
n->isNatural = FALSE;
+ n->isForce = FALSE;
n->larg = $1;
n->rarg = $4;
n->using = NIL;
n->quals = NULL;
$$ = n;
}
! | table_ref join_type opt_force JOIN table_ref join_qual
{
JoinExpr *n = makeNode(JoinExpr);
n->jointype = $2;
n->isNatural = FALSE;
+ n->isForce = $3;
n->larg = $1;
! n->rarg = $5;
! if ($6 != NULL && IsA($6, List))
! n->using = (List *) $6; /* USING clause */
else
! n->quals = $6; /* ON clause */
$$ = n;
}
| table_ref JOIN table_ref join_qual
***************
*** 7462,7467 **** joined_table:
--- 7464,7470 ----
JoinExpr *n = makeNode(JoinExpr);
n->jointype = JOIN_INNER;
n->isNatural = FALSE;
+ n->isForce = FALSE;
n->larg = $1;
n->rarg = $3;
if ($4 != NULL && IsA($4, List))
***************
*** 7475,7480 **** joined_table:
--- 7478,7484 ----
JoinExpr *n = makeNode(JoinExpr);
n->jointype = $3;
n->isNatural = TRUE;
+ n->isForce = FALSE;
n->larg = $1;
n->rarg = $5;
n->using = NIL; /* figure out which columns later... */
***************
*** 7487,7492 **** joined_table:
--- 7491,7497 ----
JoinExpr *n = makeNode(JoinExpr);
n->jointype = JOIN_INNER;
n->isNatural = TRUE;
+ n->isForce = FALSE;
n->larg = $1;
n->rarg = $4;
n->using = NIL; /* figure out which columns later... */
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 5824,5829 **** get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
--- 5824,5831 ----
{
JoinExpr *j = (JoinExpr *) jtnode;
bool need_paren_on_right;
+ char buffer[64];
+ int indentPlus;
need_paren_on_right = PRETTY_PAREN(context) &&
!IsA(j->rarg, RangeTblRef) &&
***************
*** 5834,5905 **** get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
get_from_clause_item(j->larg, query, context);
if (j->isNatural)
{
if (!PRETTY_INDENT(context))
appendStringInfoChar(buf, ' ');
! switch (j->jointype)
! {
! case JOIN_INNER:
! appendContextKeyword(context, "NATURAL JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 0);
! break;
! case JOIN_LEFT:
! appendContextKeyword(context, "NATURAL LEFT JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 0);
! break;
! case JOIN_FULL:
! appendContextKeyword(context, "NATURAL FULL JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 0);
! break;
! case JOIN_RIGHT:
! appendContextKeyword(context, "NATURAL RIGHT JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 0);
! break;
! default:
! elog(ERROR, "unrecognized join type: %d",
! (int) j->jointype);
! }
}
else
{
! switch (j->jointype)
! {
! case JOIN_INNER:
! if (j->quals)
! appendContextKeyword(context, " JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
! else
! appendContextKeyword(context, " CROSS JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 1);
! break;
! case JOIN_LEFT:
! appendContextKeyword(context, " LEFT JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
! break;
! case JOIN_FULL:
! appendContextKeyword(context, " FULL JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
! break;
! case JOIN_RIGHT:
! appendContextKeyword(context, " RIGHT JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
! break;
! default:
! elog(ERROR, "unrecognized join type: %d",
! (int) j->jointype);
! }
}
if (need_paren_on_right)
appendStringInfoChar(buf, '(');
get_from_clause_item(j->rarg, query, context);
--- 5836,5891 ----
get_from_clause_item(j->larg, query, context);
+ buffer[0] = '\0';
if (j->isNatural)
{
if (!PRETTY_INDENT(context))
appendStringInfoChar(buf, ' ');
! strcat(buffer, "NATURAL ");
! indentPlus = 0;
}
else
{
! strcat(buffer, " ");
! indentPlus = 2;
! }
!
! switch (j->jointype)
! {
! case JOIN_INNER:
! if (!j->quals && !j->isNatural)
! {
! strcat(buffer, "CROSS ");
! indentPlus = 1;
! }
! /*
! * INNER is normally just decoration, but it's required when
! * we also need to emit FORCE.
! */
! if (j->isForce)
! strcat(buffer, "INNER ");
! break;
! case JOIN_LEFT:
! strcat(buffer, "LEFT ");
! break;
! case JOIN_FULL:
! strcat(buffer, "FULL ");
! break;
! case JOIN_RIGHT:
! strcat(buffer, "RIGHT ");
! break;
! default:
! elog(ERROR, "unrecognized join type: %d", (int) j->jointype);
}
+ if (j->isForce)
+ strcat(buffer, "FORCE JOIN ");
+ else
+ strcat(buffer, "JOIN ");
+
+ appendContextKeyword(context, buffer, -PRETTYINDENT_JOIN,
+ PRETTYINDENT_JOIN, indentPlus);
+
if (need_paren_on_right)
appendStringInfoChar(buf, '(');
get_from_clause_item(j->rarg, query, context);
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
***************
*** 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
--- 1259,1264 ----
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***************
*** 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
#------------------------------------------------------------------------------
--- 219,224 ----
*** a/src/include/nodes/primnodes.h
--- b/src/include/nodes/primnodes.h
***************
*** 1150,1155 **** typedef struct JoinExpr
--- 1150,1156 ----
NodeTag type;
JoinType jointype; /* type of join */
bool isNatural; /* Natural join? Will need to shape table */
+ bool isForce; /* Join order forced? */
Node *larg; /* left subtree */
Node *rarg; /* right subtree */
List *using; /* USING clause, if any (list of String) */
*** a/src/include/optimizer/planmain.h
--- b/src/include/optimizer/planmain.h
***************
*** 79,87 **** 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);
extern void add_vars_to_targetlist(PlannerInfo *root, List *vars,
--- 79,84 ----
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
