Re: On disable_cost

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