I see; the merge join happened to be the preferred join path, so nothing
had to be excluded.
/* reset all parameters */
EXPLAIN (COSTS OFF) SELECT * FROM tab_a JOIN tab_b USING (id);
QUERY PLAN
═════════════════════════════════════
Merge Join
Merge Cond: (tab_a.id = tab_b.id)
-> Sort
Sort Key: tab_a.id
-> Seq Scan on tab_a
-> Sort
Sort Key: tab_b.id
-> Seq Scan on tab_b
So now if I disable merge joins, I should get a different strategy and see
a disabled node, right?
SET enable_mergejoin = off;
EXPLAIN (COSTS OFF) SELECT * FROM tab_a JOIN tab_b USING (id);
QUERY PLAN
════════════════════════════════════
Hash Join
Hash Cond: (tab_a.id = tab_b.id)
-> Seq Scan on tab_a
-> Hash
-> Seq Scan on tab_b
No disabled node shown... Ok, I still don't get it.
No, you don't see it.
you can see that the compare_path_costs_fuzzily function is fundamental
to determining which path will remain - new path or one of the old paths
added in the pathlist of relation (see add_path function that calls
compare_path_costs_fuzzily function).
One of the signs for it is an assessment based on the number of disabled
paths. This lines from the compare_path_costs_fuzzily function:
/* Number of disabled nodes, if different, trumps all else. */
if (unlikely(path1->disabled_nodes != path2->disabled_nodes))
{
if (path1->disabled_nodes < path2->disabled_nodes)
return COSTS_BETTER1;
else
return COSTS_BETTER2;
}
Since mergejoin is disabled for optimizer, the number of disabled nodes
are equal to 1. hashjoin is enabled and the number of its disabled nodes
are equal to 0. Thus, a hash join will be chosen since the number of
disabled nodes is less compared to a merge join.
Hashjoin is not disabled, so there are no note in the query plan that it
is disabled.
EXPLAIN (COSTS OFF) SELECT * FROM tab_a JOIN tab_b USING (id);
QUERY PLAN
════════════════════════════════════
Hash Join
Hash Cond: (tab_a.id = tab_b.id)
-> Seq Scan on tab_a
-> Hash
-> Seq Scan on tab_b
--
Regards,
Alena Rybakina
Postgres Professional
