nested loop and merge join are disabled by default in legacy planner?

[Paul Guo <pa...@gmail.com>]

  {
        {"enable_nestloop", PGC_USERSET, QUERY_TUNING_METHOD,
            gettext_noop("Enables the planner's use of nested-loop join
plans."),
            NULL
        },
        &enable_nestloop,
        false, NULL, NULL
    },
    {
        {"enable_mergejoin", PGC_USERSET, QUERY_TUNING_METHOD,
            gettext_noop("Enables the planner's use of merge join plans."),
            NULL
        },
        &enable_mergejoin,
        false, NULL, NULL
    },

I just checked greenplum/gpdb. The two guc are disabled by default also.
Anyone know the reason or history? thanks.

Re: nested loop and merge join are disabled by default in legacy planner?

[Paul Guo <pa...@gmail.com>]

Yes, thanks. I noticed the guc gp_enable_fallback_plan. That's for it.

       {"gp_enable_fallback_plan", PGC_USERSET, QUERY_TUNING_METHOD,
            gettext_noop("Plan types which are not enabled may be used when
a "
                         "query would be infeasible without them."),
            gettext_noop("If false, planner rejects queries that cannot be "
                         "satisfied using only the enabled plan types.")
        },
        &gp_enable_fallback_plan,
        true, NULL, NULL


2016-12-23 10:39 GMT+08:00 陶征霖 <zt...@apache.org>:

> Yes, in old planner only hashjoin is enabled by default, mergejoin and
> nestloop are both disabled. However hashjoin can't handle all the case,
> such as "select * from a,b where p>q" where column p and column q comes
> from table a and table b respectively. So in the first path no plan is
> generated. And then root->config->mpp_trying_fallback_plan is set to true,
> which enables all join method and try them all. You can check the function
> add_paths_to_joinrel which is called for join path generation.
>
> 2016-12-21 16:47 GMT+08:00 Paul Guo <pa...@gmail.com>:
>
> >   {
> >         {"enable_nestloop", PGC_USERSET, QUERY_TUNING_METHOD,
> >             gettext_noop("Enables the planner's use of nested-loop join
> > plans."),
> >             NULL
> >         },
> >         &enable_nestloop,
> >         false, NULL, NULL
> >     },
> >     {
> >         {"enable_mergejoin", PGC_USERSET, QUERY_TUNING_METHOD,
> >             gettext_noop("Enables the planner's use of merge join
> plans."),
> >             NULL
> >         },
> >         &enable_mergejoin,
> >         false, NULL, NULL
> >     },
> >
> > I just checked greenplum/gpdb. The two guc are disabled by default also.
> > Anyone know the reason or history? thanks.
> >
>

Re: nested loop and merge join are disabled by default in legacy planner?

[陶征霖 <zt...@apache.org>]

Yes, in old planner only hashjoin is enabled by default, mergejoin and
nestloop are both disabled. However hashjoin can't handle all the case,
such as "select * from a,b where p>q" where column p and column q comes
from table a and table b respectively. So in the first path no plan is
generated. And then root->config->mpp_trying_fallback_plan is set to true,
which enables all join method and try them all. You can check the function
add_paths_to_joinrel which is called for join path generation.

2016-12-21 16:47 GMT+08:00 Paul Guo <pa...@gmail.com>:

>   {
>         {"enable_nestloop", PGC_USERSET, QUERY_TUNING_METHOD,
>             gettext_noop("Enables the planner's use of nested-loop join
> plans."),
>             NULL
>         },
>         &enable_nestloop,
>         false, NULL, NULL
>     },
>     {
>         {"enable_mergejoin", PGC_USERSET, QUERY_TUNING_METHOD,
>             gettext_noop("Enables the planner's use of merge join plans."),
>             NULL
>         },
>         &enable_mergejoin,
>         false, NULL, NULL
>     },
>
> I just checked greenplum/gpdb. The two guc are disabled by default also.
> Anyone know the reason or history? thanks.
>