Why enable_hashjoin Completely disables HashJoin

From: Quan Zongliang <quanzongliang(at)yeah(dot)net>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Why enable_hashjoin Completely disables HashJoin
Date: 2023-04-03 10:23:41
Message-ID: 5cf6e385-259d-1755-cf80-aabd1a1b1dbd@yeah.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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,

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

Attachment Content-Type Size
joinpath.patch text/plain 1.4 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2023-04-03 10:34:28 Re: Sketch of a fix for that truncation data corruption issue
Previous Message Peter Eisentraut 2023-04-03 08:42:02 Re: SQL JSON path enhanced numeric literals