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
