PolarDB:Cost-based query transformation

Prerequisites

Before using CBQT, verify your cluster version:

• Old version of CBQT: PolarDB for MySQL 8.0.2, revision 8.0.2.2.0 or later

• New version of CBQT: PolarDB for MySQL 8.0.2, revision 8.0.2.2.19 or later

To check your revision version, see Query the engine version.

How it works

Query transformation rewrites one SQL statement into an equivalent SQL statement. For example:

SELECT *
FROM d1
    JOIN f1 ON d1.c1 = f1.c1
    LEFT JOIN (
        SELECT d2.c2 AS d2_c2, f2.c3 AS f2_c3
        FROM d2, f2
        WHERE d2.c1 = f2.c1
    ) derived
    ON derived.d2_c2 = d1.c2
        AND derived.f2_c3 = f1.c3;

After materialized table merging, this becomes:

SELECT *
FROM d1
    JOIN f1 ON d1.c1 = f1.c1
    LEFT JOIN (d2
        JOIN f2 ON TRUE)
    ON d2.c1 = f2.c1
        AND f2.c3 = f1.c3
        AND d2.c2 = d1.c2;

MySQL Community Edition applies this transformation unconditionally based on equivalence rules—without checking whether it actually reduces cost. If the indexes of d1/f1 and d2/f2 are not correlated, merging forces each row from the d1-f1 join to trigger a separate d2-f2 join, which can significantly increase execution time.

CBQT solves this by building a state space of all possible transformation combinations before committing to any. For two candidate transformations A and B, the state space contains four combinations: None, A only, B only, and AB. CBQT estimates the execution cost of each combination and selects the lowest-cost plan.

For the materialized table merging example, the execution plan after merging looks like this:

The execution plan before merging looks like this:

Whether the merged plan is better depends on whether the indexes of d2/f2 and d1/f1 are correlated, and on the size of the join result set. CBQT calculates both costs and applies the transformation only if it reduces cost.

Transformations that always reduce cost are treated as rule-based transformations and applied whenever the relevant conditions are met.

Key concepts

Nested query and nesting depth

A nested query is a query block nested inside another query. The inner query block is the subquery (also called the inner query); the enclosing block is the parent query (also called the outer query). Nesting depth counts the number of nested layers.

Example with nesting depth 2:

SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE t2.c = t1.b);

The UNION operator combines statements at the same nesting level. The following query has nesting depth 3:

SELECT *
FROM t1
WHERE t1.a IN (
    SELECT dt.b
    FROM (
        SELECT b, c FROM t2
        UNION
        SELECT b, c FROM t3
    ) dt
    WHERE dt.c = t1.b
);

Transformation

The process of rewriting a query into an equivalent form, such as the materialized table merging shown above.

Transformation object

The query element that a transformation rule applies to. For materialized table merging, the transformation object is the materialized table. For the subquery-to-SEMI JOIN transformation, it is the subquery.

Iteration

Each pass over all transformation objects is one iteration. After a transformation is applied, new objects may become eligible for further transformation—for example, a subquery that becomes a materialized table can then be merged. CBQT repeats until no new transformations are possible or until the iteration limit is reached.

Enable and configure CBQT

Two parameters control CBQT globally:

Old version of CBQT

The old version is available from revision 8.0.2.2.0.

CBQT can take time to evaluate all combinations in the state space. To protect short-lived queries, set a cost threshold: CBQT only activates when a query's estimated cost exceeds cbqt_cost_threshold.

For correlated subqueries that MySQL Community Edition does not support unnesting, the old version decorrelates them using GROUP BY clauses.

For transformations that MySQL Community Edition applies unconditionally, you can opt into cost-based evaluation. The derived merge feature is controlled by the derived_merge_cost_based flag in polar_optimizer_switch.

New version of CBQT

The new version is available from revision 8.0.2.2.19 and introduces finer-grained control over iteration count, nesting depth, search strategy, and individual transformation rules.