Re: On disable_cost

On Wed, 2024-10-02 at 21:13 +0300, Alena Rybakina wrote:
> >   CREATE TABLE tab_a (id integer);
> >
> >   CREATE TABLE tab_b (id integer);
> >
> >   SET enable_nestloop = off;
> >   SET enable_hashjoin = off;
> >
> >   EXPLAIN SELECT * FROM tab_a JOIN tab_b USING (id);
> >
> >                                QUERY PLAN
> >   ═════════════════════════════════════════════════════════════════════
> >    Merge Join  (cost=359.57..860.00 rows=32512 width=4)
> >      Merge Cond: (tab_a.id = tab_b.id)
> >      ->  Sort  (cost=179.78..186.16 rows=2550 width=4)
> >            Sort Key: tab_a.id
> >            ->  Seq Scan on tab_a  (cost=0.00..35.50 rows=2550 width=4)
> >      ->  Sort  (cost=179.78..186.16 rows=2550 width=4)
> >            Sort Key: tab_b.id
> >            ->  Seq Scan on tab_b  (cost=0.00..35.50 rows=2550 width=4)
> >
> > I would have expected to see "Disabled nodes: 2" with the merge join,
> > because both the nested loop join and the hash join have been disabled.
> >
> > Why is there no disabled node shown?
> >
> >
> >     
> >
>
>     Disabled nodes show the number of disabled paths, you simply don’t
>     have them here in mergejoin, because hashjoin and nestedloop were
>     not selected. The reason is the compare_path_costs_fuzzily function,
>     because the function decides which path is better based on fewer
>     disabled nodes. hashjoin and nestedloop have 1 more nodes compared
>     to mergejoin. you can disable mergejoin, I think the output about
>     this will appear.

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.

Yours,
Laurenz Albe