High Execution Costs of JOINs with USING-Syntax

Detail name	Value
Changelog Number	2062
Type	Improvement
Status	Open

Problem

The use of the USING-Syntax in queries containing multiple joins leads to an inefficient execution and high amount of heap memory usage by the SQL-processes.

The increase of the contained number of JOINs in the query increases the compile and execution effort exponentially. Furthermore the JOIN type does also influence the cost of the USING-Syntax. In this case OUTER JOINs combined with USING are even more expensive than INNER JOINs.

Example 1 - INNER JOIN:

select 1 from t0 
join t1 using(id) 
join t2 using(id)
join t3 using(id)
join t4 using(id)
join t5 using(id);

Example 2 - LEFT OUTER JOIN:

select 1 from t0 
join t1 using(id) 
left join t2 using(id)
left join t3 using(id)
left join t4 using(id)
left join t5 using(id);

Recommendation

For performance reasons it is recommended to use the ON-Syntax instead of USING-Syntax in JOINs .
Depending on the query only minor modifications are required.

Alternative for Example 1 - INNER JOIN:

select 1 from t0 
join t1 on t0.id = t1.id 
join t2 on t1.id = t2.id
join t3 on t2.id = t3.id
join t4 on t3.id = t4.id
join t5 on t4.id = t5.id;

Alternative for Example 2 - LEFT OUTER JOIN:

select 1 from t0 
join t1 on t0.id = t1.id 
left join t2 on t1.id = t2.id
left join t3 on t2.id = t3.id
left join t4 on t3.id = t4.id
left join t5 on t4.id = t5.id;