PolarDB:Join condition pushdown

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for MySQL 8.0 cluster running revision version 8.0.2.2.10 or later

How it works

In standard MySQL, a derived table (also called an inline view) that cannot be merged into the outer query—for example, one that uses GROUP BY, aggregate functions, or window functions—must be fully materialized before the outer join runs. When the derived table contains a large amount of data, full materialization is slow.

With join condition pushdown, PolarDB's query optimizer detects when an outer join condition references a column inside the derived table and rewrites the execution plan to apply that condition earlier, inside the derived table. The derived table can then use an index to filter rows before materialization, which reduces the data volume the outer query processes.

The optimizer uses cost calculation to decide whether pushing down a condition produces a more efficient plan. Pushdown is applied only when the indexed filtering eliminates a significant portion of rows.

When to use join condition pushdown

Join condition pushdown is most effective when both of the following are true:

• Nested-loop joins with indexed inner columns: The outer query and the derived table are joined in a nested-loop manner, and the join column inside the derived table has an index. Pushing down the join condition lets the derived table use that index to materialize only the matching rows.

• High-selectivity conditions: After the join condition is pushed down, a large fraction of rows is filtered out. The more data the index eliminates, the greater the performance gain.

Enable join condition pushdown

Use the following parameters to control join condition pushdown. For instructions on setting parameters, see Configure cluster and node parameters.

To disable join condition pushdown entirely, set join_predicate_pushdown=OFF in loose_polar_optimizer_switch. To enable it on all nodes instead of read-only nodes only, set loose_join_predicate_pushdown_opt_mode to ON.

Example

The following example shows a query that joins three derived tables. The os derived table uses a window function (ROW_NUMBER() OVER), so it cannot be merged into the outer query and must be materialized.

Before pushdown

Without join condition pushdown, os is fully materialized—all rows matching the update_date filter are scanned and aggregated before the join condition od.id = os.detail_id is applied. This query takes approximately 65 seconds.

SELECT *
FROM (
  SELECT *
  FROM sample_table.tb_order
  WHERE create_date >= DATE_SUB(CAST('2022-12-05 15:12:05' AS datetime), INTERVAL 5 MINUTE)
    AND product_type IN (2, 4)
) o
  LEFT JOIN (
    SELECT *
    FROM sample_table.tb_order_detailed
    WHERE update_time >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
  ) od
  ON o.order_id = od.order_id
  LEFT JOIN (
    SELECT t.*, row_number() OVER (PARTITION BY detail_id ORDER BY update_date DESC) AS rn
    FROM sample_table.tb_order_sku t
    WHERE update_date >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
      AND coalesce(product_type, '0') <> '5'
  ) os
  ON od.id = os.detail_id;

After pushdown

With join condition pushdown enabled, the optimizer rewrites the plan to push od.id = detail_id into the os derived table, expressed as a lateral join. The os table uses the detail_id index to filter rows before the window function runs, eliminating most of the data early. This query takes approximately 0.5 seconds.

SELECT *
FROM (
  SELECT *
  FROM db_order.tb_order
  WHERE create_date >= DATE_SUB(CAST('2022-12-05 15:12:05' AS datetime), INTERVAL 5 MINUTE)
    AND product_type IN (2, 4)
) o
  LEFT JOIN (
    SELECT *
    FROM db_order.tb_order_detailed
    WHERE update_time >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
  ) od
  ON o.order_id = od.order_id
  LEFT JOIN LATERAL((
    SELECT t.*, row_number() OVER (PARTITION BY detail_id ORDER BY update_date DESC) AS rn
    FROM db_order.tb_order_sku t
    WHERE update_date >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
      AND coalesce(product_type, '0') <> '5'
      AND od.id = detail_id
  )) os;

The key change is AND od.id = detail_id inside the os subquery, combined with the LEFT JOIN LATERAL syntax. This lets the optimizer apply the index-based filter before materializing os, rather than after.

If your query does not benefit from pushdown despite meeting the general criteria, confirm that the join column inside the derived table is indexed and that the join condition has high selectivity.