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