Re: Why enable_hashjoin Completely disables HashJoin

On 2023/4/3 19:44, Tomas Vondra wrote:
> On 4/3/23 12:23, Quan Zongliang wrote:
>> Hi,
>>
>> I found that the enable_hashjoin disables HashJoin completely.
>> It's in the function add_paths_to_joinrel:
>>
>> if (enable_hashjoin || jointype == JOIN_FULL)
>>     hash_inner_and_outer(root, joinrel, outerrel, innerrel,
>>                 jointype, &extra);
>>
>> Instead, it should add a disable cost to the cost calculation of
>> hashjoin. And now final_cost_hashjoin does the same thing:
>>
>> if (!enable_hashjoin)
>>     startup_cost += disable_cost;
>>
>>
>> enable_mergejoin has the same problem.
>>
>> Test case:
>>
>> CREATE TABLE t_score_01(
>> s_id int,
>> s_score int,
>> s_course char(8),
>> c_id int);
>>
>> CREATE TABLE t_student_01(
>> s_id int,
>> s_name char(8));
>>
>> insert into t_score_01 values(
>> generate_series(1, 1000000), random()*100, 'course', generate_series(1,
>> 1000000));
>>
>> insert into t_student_01 values(generate_series(1, 1000000), 'name');
>>
>> analyze t_score_01;
>> analyze t_student_01;
>>
>> SET enable_hashjoin TO off;
>> SET enable_nestloop TO off;
>> SET enable_mergejoin TO off;
>>
>> explain select count(*)
>> from t_student_01 a join t_score_01 b on a.s_id=b.s_id;
>>
>> After disabling all three, the HashJoin path should still be chosen.
>>
>
> It's not clear to me why that behavior would be desirable? Why is this
> an issue you need so solve?
>
Because someone noticed that when he set enable_hashjoin,
enable_mergejoin and enable_nestloop to off. The statement seemed to get
stuck (actually because it chose the NestedLoop path, which took a long
long time to run).
If enable_hashjoin and enable_nestloop disable generating these two
paths. Then enable_nestloop should do the same thing, but it doesn't.

> AFAIK the reason why some paths are actually disabled (not built at all)
> while others are only penalized by adding disable_cost is that we need
> to end up with at least one way to execute the query. So we pick a path
> that we know is possible (e.g. seqscan) and hard-disable other paths.
> But the always-possible path is only soft-disabled by disable_cost.
>
> For joins, we do the same thing. The hash/merge joins may not be
> possible, because the data types may not have hash/sort operators, etc.
> Nestloop is always possible. So we soft-disable nestloop but
> hard-disable hash/merge joins.
>
> I doubt we want to change this behavior, unless there's a good reason to
> do that ...
It doesn't have to change. Because selecting NestedLoop doesn't really
get stuck either. It just takes too long to run.

I will change the patch status to Withdrawn.
>
>
> regards
>