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 |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
joinpath.patch | text/plain | 1.4 KB |
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 |