I think we should try to do something about join_collapse_limit,
from_collapse_limit, and geqo_threshold for 8.5.
http://archives.postgresql.org/message-id/9134.1243289...@sss.pgh.pa.us
http://archives.postgresql.org/message-id/603c8f070905251800g5b86d2dav26eca7f417d15...@mail.gmail.com
I'm still of the opinion that join_collapse_threshold is a loaded
foot-gun, because I don't think that users will expect that a join
specified this way:
SELECT ... FROM a JOIN b ON Pab JOIN c ON Pac JOIN d ON Pad ...
will behave differently than one specified this way:
SELECT ... FROM a, b, c, d WHERE Pab AND Pac AND Pad ...
The whole purpose of join_collapse_limit in the first instance is to
prevent planning time from getting out of control, but I don't see how
we can view it as a very effective safety valve when it depends so
heavily on which syntax is used. If the planning time for an N-way
join is excessive, then we're going to have a problem with excessive
planning time whenever the second syntax is selected, and I don't see
any reason to believe that users see the second syntax as "dangerous"
in terms of planning time but the first syntax as "safer".
One possibility would be to remove join_collapse_limit entirely, but
that would eliminate one possibily-useful piece of functionality that
it current enables: namely, the ability to exactly specify the join
order by setting join_collapse_limit to 1. So one possibility would
be to rename the variable something like explicit_join_order and make
it a Boolean; another possibility would be to change the default value
to INT_MAX.
The approach I've taken in the attached patch is to make 0 mean
"unlimited" and make that the default value. I don't have a strong
feeling about whether that's better than the other two options,
although it seems cleaner to me or I'd not have written the patch that
way. We could also consider adopting this same approach for
from_collapse_limit, though for some reason that behavior marginally
less pathological to me.
At any rate, regardless of whether this patch (or one of the other
approaches mentioned above) are adopted for 8.5, I think we should
raise the default values for whatever is left. The defaults basically
haven't been modified since they were put in, and my experience is
that even queries with 10 to 15 joins perform acceptably for OLTP
workloads, which are exactly the workloads where query planning time
is most likely to be an issue. So I would propose raising each of the
limits by 4 (to 12 for from_collapse_limit and join_collapse_limit if
we don't unlimit them entirely, and to 16 for geqo_threshold). I'm
interested in hearing from anyone who has practical experience with
tuning these variables, or any ideas on what we should test to get a
better idea as to how to set them.
Thanks,
...Robert
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 2288,2296 **** SELECT * FROM parent WHERE key = 2400;
</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
--- 2288,2295 ----
</para>
<para>
! By default, this variable is set to <literal>0</>, which always
! allows rewriting. 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
*** a/src/backend/optimizer/plan/initsplan.c
--- b/src/backend/optimizer/plan/initsplan.c
***************
*** 477,483 **** deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
/* 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 */
--- 477,484 ----
/* force the join order exactly at this node */
joinlist = list_make1(list_make2(leftjoinlist, rightjoinlist));
}
! else if (join_collapse_limit == 0
! || list_length(leftjoinlist) + list_length(rightjoinlist) <=
join_collapse_limit)
{
/* OK to combine subproblems */
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
***************
*** 1275,1284 **** static struct config_int ConfigureNamesInt[] =
"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,
--- 1275,1285 ----
"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. "
! "Zero indicates no limit.")
},
&join_collapse_limit,
! 0, 0, INT_MAX, NULL, NULL
},
{
{"geqo_threshold", PGC_USERSET, QUERY_TUNING_GEQO,
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***************
*** 220,227 ****
#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,229 ----
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
! #join_collapse_limit = 0 # 1 disables collapsing of explicit
! # JOIN clauses, 0 always allows collapsing,
! # >1 allows collapsing of lists when FROM clause will
! # have <= join_collapse_limit items
#------------------------------------------------------------------------------
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
