PolarDB:Adaptive execution plan switching

How it works

Both modes follow the same three-phase mechanism:

1. Optimization phase: The optimizer marks row count thresholds for each query block and the entire query, then adds them to monitoring metadata. If the optimizer does not select IMCI, or selects an ordered index due to prefer_ordering_index, it calculates the threshold for triggering a plan switch.

2. Execution phase: As the query runs, the database tracks rows scanned in the critical path using integer comparisons only—no performance overhead. When the scanned row count reaches the threshold, the system evaluates whether to switch to an alternative plan.

3. Plan switch: Before switching, the database ensures that no partial results have been returned to the client. After switching, it clears any cached intermediate results and continues execution under the new plan.

For clusters running version 8.0.1.1.49 or later (or 8.0.2.2.29 or later), the threshold for adaptive row-column routing is controlled by loose_adaptive_cost_threshold_for_imci. For earlier versions, it is derived from loose_cost_threshold_for_imci.

The following diagram illustrates the flow for adaptive row-column routing.

Prerequisites

Before enabling adaptive execution plan switching, make sure your cluster meets the version requirements for the mode you want to use.

Adaptive row-column routing (imci_chosen=on):

• PolarDB for MySQL 8.0.1, revision version 8.0.1.1.39 or later

• PolarDB for MySQL 8.0.2, revision version 8.0.2.2.29 or later

Adaptive selection of ordered indexes (ordering_index=on):

• PolarDB for MySQL 8.0.1, revision version 8.0.1.1.47 or later

• PolarDB for MySQL 8.0.2, revision version 8.0.2.2.29 or later

Enable or disable adaptive execution plan switching

Log in to the PolarDB console and go to the Parameters page of your cluster. For configuration steps, see Configure cluster and node parameters.

Set loose_adaptive_plans_switch to one of the following values:

• 'imci_chosen=on' — enables adaptive row-column routing

• 'ordering_index=on' — enables adaptive selection of ordered indexes

• 'imci_chosen=on,ordering_index=on' — enables both modes

• Set either sub-key to 'off' to disable the corresponding mode

The following table describes all configurable parameters.

Why the default solutions fall short

Before adaptive execution plan switching was available, addressing misrouted queries or poor index selection required one of two manual approaches:

• Lower the column store cost threshold globally: This routes more queries to column store nodes, but also sends short, lightweight queries to IMCI—increasing load on column store nodes and potentially blocking other workloads.

• Use statement outline to fix specific query templates: The statement outline feature can force a specific query to use column store or disable prefer_ordering_index:

  -- Force a query to use column store
  CALL dbms_outln.add_optimizer_outline("<schema_name>", "/*+ SET_VAR(cost_threshold_for_imci=0) SET_VAR(imci_ap_threshold=0) */", "<query>");
  
  -- Disable prefer_ordering_index for a specific query template
  CALL dbms_outln.add_optimizer_outline("<schema_name>", "/*+ SET_VAR(optimizer_switch='prefer_ordering_index=off') */", "<query>");

  This approach is precise but adds significant operational costs—each problematic query template must be identified and managed individually.

Adaptive execution plan switching addresses both problems automatically: it switches plans only when actual runtime data confirms the optimizer's estimate was wrong, leaving correctly estimated queries unaffected.

When to use each approach: Use loose_adaptive_plans_switch (automatic) as the default—it requires no per-query configuration and responds to runtime evidence. Use statement outline (manual) when you need deterministic behavior for a specific query template, regardless of runtime statistics.

Verify adaptive switching activity

Run the following statement to check the total number of adaptive plan switches since the feature was enabled:

SHOW GLOBAL STATUS LIKE 'Adaptive_plan_used';

What's next

• In-Memory Column Index (IMCI)

• Automatic row-column routing

• Statement outline

• Configure cluster and node parameters