Why enable_hashjoin Completely disables HashJoin

Поиск
Список
Период
Сортировка
От Quan Zongliang
Тема Why enable_hashjoin Completely disables HashJoin
Дата
Msg-id 5cf6e385-259d-1755-cf80-aabd1a1b1dbd@yeah.net
обсуждение исходный текст
Ответы Re: Why enable_hashjoin Completely disables HashJoin  (Tomas Vondra <tomas.vondra@enterprisedb.com>)
Re: Why enable_hashjoin Completely disables HashJoin  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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
Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Peter Eisentraut
Дата:
Сообщение: Re: SQL JSON path enhanced numeric literals
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: Sketch of a fix for that truncation data corruption issue