In which scenarios can LEFT JOIN be changed to RIGHT JOIN to optimize query performance - AnalyticDB

LEFT JOIN is a commonly used table join method. When a hash join is performed, the right table is used to build the hash table, and the order of the left and right tables of a LEFT JOIN cannot be changed. If the right table is large, query execution becomes slow and consumes a large amount of memory. This topic uses specific examples to describe the scenarios in which a LEFT JOIN can be changed to a RIGHT JOIN.

Background information

AnalyticDB for MySQL uses hash joins to join tables by default. In a hash join, the right table is used to build the hash table, which consumes a large amount of resources. Unlike inner joins, outer joins (including LEFT JOIN and RIGHT JOIN) cannot exchange the order of the left and right tables without changing query semantics. Therefore, if the right table is large, query execution becomes slow and consumes a large amount of memory. In extreme cases where the right table is very large, cluster performance is affected, or the Out of Memory Pool size pre cal error is reported during query execution. In this case, you can use the optimization methods provided in this topic to reduce resource consumption.

Scenarios

You can change a LEFT JOIN to a RIGHT JOIN by modifying the SQL statement or adding a hint. In the original LEFT JOIN, the left table becomes the right table to build the hash table. If the new right table is too large, performance is still affected. Therefore, we recommend that you perform this optimization when the left table of the LEFT JOIN is small and the right table is large.

Whether a table is small or large is relative and depends on factors such as join columns and cluster resources. In practice, you can use EXPLAIN ANALYZE to view the parameters of the execution plan. To determine whether to use a RIGHT JOIN, observe changes in parameters such as PeakMemory and WallTime.

Usage

You can change a LEFT JOIN to a RIGHT JOIN in one of the following two ways:

Modify the SQL statement directly. For example, change a left join b on a.col1 = b.col2 to b right join a on a.col1 = b.col2.
Add a hint to instruct the optimizer to change a LEFT JOIN to a RIGHT JOIN based on resource consumption. In this way, the optimizer determines whether to change a LEFT JOIN to a RIGHT JOIN based on the estimated sizes of the left and right tables. Use one of the following methods:
- For clusters of V3.1.8 or later, this feature is enabled by default. If the feature is disabled, add the following hint to the beginning of the SQL statement to manually enable it: /*+O_CBO_RULE_SWAP_OUTER_JOIN=true*/
- For clusters of a version earlier than V3.1.8, this feature is disabled by default. Add the following hint to the beginning of the SQL statement to enable it: /*+LEFT_TO_RIGHT_ENABLED=true*/

Note

To check the minor version of an Data Lakehouse Edition cluster, run SELECT adb_version();. To upgrade the minor version, see Update the minor version of a cluster.

Examples

In the following example, nation is a small table with 25 rows and customer is a large table with 15,000,000 rows. Use EXPLAIN ANALYZE to view the execution plan of a query that contains a LEFT JOIN.

explain analyze
SELECT
  COUNT(*)
FROM
  nation t1
  left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey

The following plan shows stage 2, which performs the join calculation. The Left Join operator contains the following information:

PeakMemory: 515MB (93.68%), WallTime: 4.34s (43.05%): the PeakMemory value accounts for up to 93.68% of the total, which indicates that the LEFT JOIN is the performance bottleneck of the query.
Left (probe) Input avg.: 0.52 rows; Right (build) Input avg.: 312500.00 rows: the right table is the large table and the left table is the small table.

In this scenario, you can change the LEFT JOIN to a RIGHT JOIN to optimize the query.

Fragment 2 [HASH]
    Output: 48 rows (432B), PeakMemory: 516MB, WallTime: 6.52us, Input: 15000025 rows (200.27MB); per task: avg.: 2500004.17 std.dev.: 2410891.74
    Output layout: [count_0_2]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count_0_2:bigint]
    │   Estimates: {rows: ? (?)}
    │   Output: 96 rows (864B), PeakMemory: 96B (0.00%), WallTime: 88.21ms (0.88%)
    │   count_2 := count(*)
    └─ LEFT Join[(`n_nationkey` = `c_nationkey`)][$hashvalue, $hashvalue_0_4]
       │   Outputs: []
       │   Estimates: {rows: 15000000 (0B)}
       │   Output: 30000000 rows (200.27MB), PeakMemory: 515MB (93.68%), WallTime: 4.34s (43.05%)
       │   Left (probe) Input avg.: 0.52 rows, Input std.dev.: 379.96%
       │   Right (build) Input avg.: 312500.00 rows, Input std.dev.: 380.00%
       │   Distribution: PARTITIONED
       ├─ RemoteSource[3]
       │      Outputs: [n_nationkey:integer, $hashvalue:bigint]
       │      Estimates: 
       │      Output: 25 rows (350B), PeakMemory: 64KB (0.01%), WallTime: 63.63us (0.00%)
       │      Input avg.: 0.52 rows, Input std.dev.: 379.96%
       └─ LocalExchange[HASH][$hashvalue_0_4] ("c_nationkey")
          │   Outputs: [c_nationkey:integer, $hashvalue_0_4:bigint]
          │   Estimates: {rows: 15000000 (57.22MB)}
          │   Output: 30000000 rows (400.54MB), PeakMemory: 10MB (1.84%), WallTime: 1.81s (17.93%)
          └─ RemoteSource[4]
                 Outputs: [c_nationkey:integer, $hashvalue_0_5:bigint]
                 Estimates: 
                 Output: 15000000 rows (200.27MB), PeakMemory: 3MB (0.67%), WallTime: 191.32ms (1.90%)
                 Input avg.: 312500.00 rows, Input std.dev.: 380.00%

Change the LEFT JOIN to a RIGHT JOIN by modifying the SQL statement:

SELECT
  COUNT(*)
FROM
  customer t2
  right JOIN nation t1 ON t1.n_nationkey = t2.c_nationkey

Change the LEFT JOIN to a RIGHT JOIN by adding a hint:

For clusters of V3.1.8 or later, run the following statement to enable this feature:

/*+O_CBO_RULE_SWAP_OUTER_JOIN=true*/
SELECT
  COUNT(*)
FROM
  nation t1
  left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey

For clusters of a version earlier than V3.1.8, run the following statement to enable this feature:

/*+LEFT_TO_RIGHT_ENABLED=true*/
SELECT
  COUNT(*)
FROM
  nation t1
  left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey

After you run EXPLAIN ANALYZE on any of the preceding queries, the execution plan shows a RIGHT Join instead of a LEFT Join, which indicates that the hint takes effect. After the adjustment, the PeakMemory value is 889 KB (3.31%), which decreases from 515 MB to 889 KB and is no longer a performance hotspot.

Fragment 2 [HASH]
    Output: 96 rows (864B), PeakMemory: 12MB, WallTime: 4.27us, Input: 15000025 rows (200.27MB); per task: avg.: 2500004.17 std.dev.: 2410891.74
    Output layout: [count_0_2]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count_0_2:bigint]
    │   Estimates: {rows: ? (?)}
    │   Output: 192 rows (1.69kB), PeakMemory: 456B (0.00%), WallTime: 5.31ms (0.08%)
    │   count_2 := count(*)
    └─ RIGHT Join[(`c_nationkey` = `n_nationkey`)][$hashvalue, $hashvalue_0_4]
       │   Outputs: []
       │   Estimates: {rows: 15000000 (0B)}
       │   Output: 15000025 rows (350B), PeakMemory: 889KB (3.31%), WallTime: 3.15s (48.66%)
       │   Left (probe) Input avg.: 312500.00 rows, Input std.dev.: 380.00%
       │   Right (build) Input avg.: 0.52 rows, Input std.dev.: 379.96%
       │   Distribution: PARTITIONED
       ├─ RemoteSource[3]
       │      Outputs: [c_nationkey:integer, $hashvalue:bigint]
       │      Estimates: 
       │      Output: 15000000 rows (200.27MB), PeakMemory: 3MB (15.07%), WallTime: 634.81ms (9.81%)
       │      Input avg.: 312500.00 rows, Input std.dev.: 380.00%
       └─ LocalExchange[HASH][$hashvalue_0_4] ("n_nationkey")
          │   Outputs: [n_nationkey:integer, $hashvalue_0_4:bigint]
          │   Estimates: {rows: 25 (100B)}
          │   Output: 50 rows (700B), PeakMemory: 461KB (1.71%), WallTime: 942.37us (0.01%)
          └─ RemoteSource[4]
                 Outputs: [n_nationkey:integer, $hashvalue_0_5:bigint]
                 Estimates: 
                 Output: 25 rows (350B), PeakMemory: 64KB (0.24%), WallTime: 76.34us (0.00%)
                 Input avg.: 0.52 rows, Input std.dev.: 379.96%