PolarDB-X caches execution plans for parameterized SQL statements to keep complex query performance stable across version updates. This mechanism consists of two layers: plan cache and SQL Plan Management (SPM).
How it works
When PolarDB-X receives a SQL statement, it processes the statement in this order:
Parameterize: Replace all constant values in the statement with the placeholder character (
?).Check plan cache: Use the parameterized SQL as a key to look up a cached execution plan. If no cached plan exists, invoke the optimizer to generate one.
Route simple statements: Execute simple statements directly. Execution plan management applies only to complex statements.
Select the best plan for complex statements: Execute the statement using the fixed execution plan stored in the baseline. If multiple plans exist in the baseline, the Plan Enumerator selects the one with the lowest cost.
Plan cache
Plan cache is enabled by default in PolarDB-X. After parameterization, PolarDB-X replaces all constants in a SQL statement with ? and builds a parameter list. In the execution plan, the SQL inside the LogicalView operator contains these ? placeholders.
Run EXPLAIN on any statement to check whether it hits the plan cache. The HitCache field in the output indicates the result:
EXPLAIN SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';The output includes a HitCache:true or HitCache:false indicator.
Execution plan management
After a complex SQL statement passes through plan cache, SQL Plan Management (SPM) applies execution plan management to it.
Both plan cache and execution plan management use the parameterized SQL statement as the lookup key. Plan cache covers all SQL statements; execution plan management applies only to complex queries.
Each SQL statement maps to exactly one baseline. A baseline holds one or more execution plans. SPM evaluates the cost of each plan based on the current parameter values and selects the lowest-cost plan. When an execution plan from plan cache enters SPM:
If the plan is known, SPM checks whether it has the lowest cost.
If the plan is unknown, SPM evaluates it to determine whether optimization is needed.
During automatic plan evolution, SPM adds better plans discovered by the Plan Enumerator to the baseline automatically.
BASELINE commands
PolarDB-X provides the BASELINE command family to manage execution plans.
Syntax:
BASELINE (LOAD|PERSIST|CLEAR|VALIDATE|LIST|DELETE) [Signed Integer, Signed Integer, ...]
BASELINE (ADD|FIX) SQL (HINT Select Statement)Command reference:
| Command | Description |
|---|---|
BASELINE ADD SQL <hint> <select> | Adds an execution plan generated with a hint to the baseline. Both the new plan and existing plans remain available; the Plan Enumerator selects the lowest-cost one at runtime. |
BASELINE FIX SQL <hint> <select> | Fixes an execution plan as the mandatory plan. PolarDB-X always uses this plan regardless of parameter values. |
BASELINE LIST | Lists all baselines and their execution plans. |
BASELINE LOAD | Loads the specified baseline from the system table into memory. |
BASELINE LOAD_PLAN | Loads the specified execution plan from the system table into memory. |
BASELINE PERSIST | Flushes the specified baseline to disk. |
BASELINE PERSIST_PLAN | Flushes the specified execution plan to disk. |
BASELINE CLEAR | Removes the specified baseline from memory. |
BASELINE CLEAR_PLAN | Removes the specified execution plan from memory. |
BASELINE DELETE | Deletes the specified baseline from disk. |
BASELINE DELETE_PLAN | Deletes the specified execution plan from disk. |
`BASELINE LIST` output columns:
| Column | Description |
|---|---|
BASELINE_ID | Unique identifier for the baseline, derived from the parameterized SQL. |
PARAMETERIZED_SQL | The SQL statement after all constants are replaced with ?. |
PLAN_ID | Unique identifier for the execution plan within this baseline. |
EXTERNALIZED_PLAN | The serialized logical execution plan tree. |
FIXED | 1 if this plan is fixed by BASELINE FIX and always selected, regardless of cost. 0 if the plan participates in cost-based selection. |
ACCEPTED | 1 if this plan is accepted and eligible for selection by the Plan Enumerator. 0 if the plan is pending verification. |
A baseline can contain multiple plans withACCEPTED=1. When no plan hasFIXED=1, the Plan Enumerator selects the lowest-cost plan at runtime. When a plan hasFIXED=1, PolarDB-X always uses that plan, ignoring cost.
Optimize execution plans
After data changes or a PolarDB-X optimizer upgrade, a better execution plan may exist for a query. Use BASELINE ADD and BASELINE FIX to introduce and pin better plans.
The following example shows how to switch a Hash Join query to a Batched Key Access join (BKA join) and fix that plan.
Step 1: Check the current execution plan
Run EXPLAIN to see the current plan and verify it hits the plan cache.
EXPLAIN SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';Output:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL PLAN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Gather(parallel=true) |
| ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") |
| LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) |
| LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true) |
| HitCache:true |
| |
| |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.06 sec)Run BASELINE LIST to see the baseline. It contains one plan (ParallelHashJoin), with FIXED=0 and ACCEPTED=1.
BASELINE LIST;Output:
+-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
| BASELINE_ID | PARAMETERIZED_SQL | PLAN_ID | EXTERNALIZED_PLAN | FIXED | ACCEPTED |
+-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
| -399023558 | SELECT *
FROM lineitem
JOIN part ON l_partkey = p_partkey
WHERE p_name LIKE ? | -935671684 |
Gather(parallel=true)
ParallelHashJoin(condition="l_partkey = p_partkey", type="inner")
LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true)
LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true)
| 0 | 1 |
+-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
1 row in set (0.02 sec)Step 2: Verify the alternative plan with a hint
Use the /*+TDDL:BKA_JOIN(lineitem, part)*/ hint to generate a BKA join plan without modifying the baseline.
EXPLAIN /*+TDDL:bka_join(lineitem, part)*/ SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';Output:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL PLAN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Gather(parallel=true) |
| ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") |
| LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) |
| Gather(concurrent=true) |
| LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)") |
| HitCache:false |
| |
| |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.14 sec)HitCache:false confirms the hint-driven plan is not yet in the baseline. The baseline is unchanged.
Step 3: Add the BKA join plan to the baseline
Run BASELINE ADD to add the BKA join plan alongside the existing Hash Join plan. The Plan Enumerator selects between them at runtime based on cost.
BASELINE ADD SQL /*+TDDL:bka_join(lineitem, part)*/ SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';Output:
+-------------+--------+
| BASELINE_ID | STATUS |
+-------------+--------+
| -399023558 | OK |
+-------------+--------+
1 row in set (0.09 sec)BASELINE LIST now shows two plans for the same baseline ID — the BKA join plan (PLAN_ID: -1024543942) and the original Hash Join plan (PLAN_ID: -935671684). Both have FIXED=0 and ACCEPTED=1.
BASELINE LIST;Output:
+-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
| BASELINE_ID | PARAMETERIZED_SQL | PLAN_ID | EXTERNALIZED_PLAN | FIXED | ACCEPTED |
+-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
| -399023558 | SELECT *
FROM lineitem
JOIN part ON l_partkey = p_partkey
WHERE p_name LIKE ? | -1024543942 |
Gather(parallel=true)
ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner")
LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true)
Gather(concurrent=true)
LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)")
| 0 | 1 |
| -399023558 | SELECT *
FROM lineitem
JOIN part ON l_partkey = p_partkey
WHERE p_name LIKE ? | -935671684 |
Gather(parallel=true)
ParallelHashJoin(condition="l_partkey = p_partkey", type="inner")
LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true)
LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true)
| 0 | 1 |
+-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+
2 rows in set (0.03 sec)As the p_name LIKE ? value changes, PolarDB-X selects different execution plans dynamically.
Step 4: Fix the BKA join plan
To always use the BKA join plan regardless of parameter values, run BASELINE FIX.
BASELINE FIX SQL /*+TDDL:bka_join(lineitem, part)*/ SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';Output:
+-------------+--------+
| BASELINE_ID | STATUS |
+-------------+--------+
| -399023558 | OK |
+-------------+--------+
1 row in set (0.07 sec)After fixing, BASELINE LIST shows the BKA join plan with FIXED=1 and the Hash Join plan with FIXED=0.
mysql> baseline list\G
*************************** 1. row ***************************
BASELINE_ID: -399023558
PARAMETERIZED_SQL: SELECT *
FROM lineitem
JOIN part ON l_partkey = p_partkey
WHERE p_name LIKE ?
PLAN_ID: -1024543942
EXTERNALIZED_PLAN:
Gather(parallel=true)
ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner")
LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true)
Gather(concurrent=true)
LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)")
FIXED: 1
ACCEPTED: 1
*************************** 2. row ***************************
BASELINE_ID: -399023558
PARAMETERIZED_SQL: SELECT *
FROM lineitem
JOIN part ON l_partkey = p_partkey
WHERE p_name LIKE ?
PLAN_ID: -935671684
EXTERNALIZED_PLAN:
Gather(parallel=true)
ParallelHashJoin(condition="l_partkey = p_partkey", type="inner")
LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true)
LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true)
FIXED: 0
ACCEPTED: 1
2 rows in set (0.01 sec)Step 5: Verify the fixed plan
Run EXPLAIN again without any hint.
EXPLAIN SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';Output:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL PLAN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Gather(parallel=true) |
| ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") |
| LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) |
| Gather(concurrent=true) |
| LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)") |
| HitCache:true |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)HitCache:true and ParallelBKAJoin confirm that PolarDB-X now always uses the fixed BKA join plan, with no hint required.