DDL:
recreate table t1 (id int primary key, col int);
create index t1_col on t1 (col);
recreate table t2 (id int primary key, col int);
create index t2_col on t2 (col);
recreate table t3 (id int primary key, col int);
create index t3_col on t3 (col);
Test case:
select *
from t1 t1
inner join t2 t2 on t1.id = t2.id
inner join t3 t3 on t1.id = t3.id
where
t1.col = 0
and t2.col = 0
and t3.col = 0;
-- PLAN JOIN (T1 INDEX (T1_COL), T2 INDEX (RDB$PRIMARY11), T3 INDEX (RDB$PRIMARY12))
select *
from t1 t1
inner join t2 t2 on t1.id = t2.id
inner join t3 t3 on t2.id = t3.id
where
t1.col = 0
and t2.col = 0
and t3.col = 0;
-- PLAN JOIN (T1 INDEX (T1_COL), T2 INDEX (RDB$PRIMARY11), T3 INDEX (RDB$PRIMARY12))
select *
from t1 t1
inner join t2 t2 on t1.id = t2.id
inner join t3 t3 on t1.id = t3.id and t2.id = t3.id
where
t1.col = 0
and t2.col = 0
and t3.col = 0;
-- PLAN JOIN (T1 INDEX (T1_COL), T2 INDEX (RDB$PRIMARY11), T3 INDEX (RDB$PRIMARY12))
select *
from t1 t1
inner join t2 t2 using (id)
inner join t3 t3 using (id)
where
t1.col = 0
and t2.col = 0
and t3.col = 0;
-- PLAN HASH (JOIN (T1 INDEX (T1_COL), T2 INDEX (RDB$PRIMARY11)), T3 INDEX (T3_COL))
The last query with USING syntax gets a different (usually worse) plan. This issue is closely related to #3357, because using (id) is internally transformed into coalesce(t1.id, t2.id) = t3.id. But I intentionally create a separate ticket, because the JOIN .. USING case is more widely used and most people are unaware of the aforementioned transformation.
Of course, the issue does not exist for only two tables joined, or if the matching column names are unique for the every join part.
DDL:
Test case:
The last query with USING syntax gets a different (usually worse) plan. This issue is closely related to #3357, because
using (id)is internally transformed intocoalesce(t1.id, t2.id) = t3.id. But I intentionally create a separate ticket, because the JOIN .. USING case is more widely used and most people are unaware of the aforementioned transformation.Of course, the issue does not exist for only two tables joined, or if the matching column names are unique for the every join part.