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
