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.
Attached is the patch file.
--
Quan Zongliang
Vastdata
diff --git a/src/backend/optimizer/path/joinpath.c
b/src/backend/optimizer/path/joinpath.c
index bd51e4f972..4b5ab7f7ff 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -206,14 +206,13 @@ add_paths_to_joinrel(PlannerInfo *root,
* way of implementing a full outer join, so override enable_mergejoin
if
* it's a full join.
*/
- if (enable_mergejoin || jointype == JOIN_FULL)
- extra.mergeclause_list = select_mergejoin_clauses(root,
-
joinrel,
-
outerrel,
-
innerrel,
-
restrictlist,
-
jointype,
-
&mergejoin_allowed);
+ extra.mergeclause_list = select_mergejoin_clauses(root,
+
joinrel,
+
outerrel,
+
innerrel,
+
restrictlist,
+
jointype,
+
&mergejoin_allowed);
/*
* If it's SEMI, ANTI, or inner_unique join, compute correction factors
@@ -316,9 +315,8 @@ add_paths_to_joinrel(PlannerInfo *root,
* before being joined. As above, disregard enable_hashjoin for full
* joins, because there may be no other alternative.
*/
- if (enable_hashjoin || jointype == JOIN_FULL)
- hash_inner_and_outer(root, joinrel, outerrel, innerrel,
- jointype, &extra);
+ hash_inner_and_outer(root, joinrel, outerrel, innerrel,
+ jointype, &extra);
/*
* 5. If inner and outer relations are foreign tables (or joins)
belonging
