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? 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 ... regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
