How to use Hybrid Plan to accelerate wide table queries - PolarDB

Wide table queries that filter on a few columns but retrieve all columns create a read amplification problem: a columnstore index handles filtering efficiently, but fetching every projected column through it is expensive. A row store index avoids that amplification, but loses columnar filtering speed. Hybrid Plan combines both indexes in a single query execution to get the best of each.

How it works

Hybrid Plan splits execution into two stages:

The optimizer uses the In-Memory Column Index (IMCI) to filter rows and retrieve their primary keys.
The system uses those primary keys to look up the full row data from the InnoDB primary index.

This eliminates read amplification: IMCI handles the fast columnar scan, and InnoDB returns only the matching rows in full.

Applicability

Cluster edition: PolarDB for MySQL Enterprise Edition
Supported versions:
- PolarDB for MySQL 8.0.1, revision 8.0.1.1.37 or later
- PolarDB for MySQL 8.0.2, revision 8.0.2.2.23.1 or later

Use cases

Wide table queries that return 15 or more columns.

Limitations

Tables that provide output columns for the query must have an explicitly defined primary key.

Note

If Hybrid Plan cannot accelerate a slow SQL statement due to limitations such as the table type, contact us for help.

Parameters

Two parameters control Hybrid Plan. They work at different levels and follow a clear priority order:

imci_enable_hybrid_plan — controls the feature globally. When set to ON, the optimizer evaluates each query and automatically applies Hybrid Plan when it estimates a cost benefit.
imci_optimizer_switch — a per-query hint. When force_hybrid_index_search=ON is set in a hint, it forces Hybrid Plan for that statement regardless of the optimizer's cost estimate.

Priority: A per-query hint (force_hybrid_index_search=ON) overrides the optimizer's automatic decision. The optimizer only decides when no hint is present and imci_enable_hybrid_plan is ON.

Parameter	Default	Description
`imci_enable_hybrid_plan`	ON	Enables Hybrid Plan globally. Set to OFF to disable the feature.
`imci_optimizer_switch`	`force_hybrid_index_search=OFF`	Per-query override. Set `force_hybrid_index_search=ON` in a hint to force Hybrid Plan for a specific statement.

Enable and verify Hybrid Plan

This section uses SELECT * FROM t1; as an example.

Step 1: Connect to your cluster

Connect to the PolarDB cluster. For details, see Connect to a database cluster.

Step 2: Enable the feature

Set imci_enable_hybrid_plan to ON. The optimizer then automatically applies Hybrid Plan to eligible wide table queries.

SET imci_enable_hybrid_plan=ON;

Step 3: Force a specific query to use Hybrid Plan (optional)

If the optimizer does not select Hybrid Plan for a query (for example, its cost estimate favors another plan), add a hint to override the decision:

SELECT /*+ SET_VAR(imci_optimizer_switch='force_hybrid_index_search=ON') */ * FROM t1;

Step 4: Verify that Hybrid Plan is active

Run EXPLAIN on the query to confirm that Hybrid Plan is in use. When active, the execution plan includes the HybridIndexSearch operator.

EXPLAIN SELECT /*+ SET_VAR(imci_optimizer_switch='force_hybrid_index_search=ON') */ * FROM t1;

Expected output:

+----+-------------------------+------+---------------------------------------------------------------+
| ID | Operator                | Name | Extra Info                                                    |
+----+-------------------------+------+---------------------------------------------------------------+
|  1 | Select Statement        |      | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited) |
|  2 | └─HybridIndexSearch     |      | Used table: t1                                                |
|  3 |   └─Compute Scalar      |      |                                                               |
|  4 |     └─Table Scan        | t1   |                                                               |
+----+-------------------------+------+---------------------------------------------------------------+
4 rows in set (0.02 sec)

The HybridIndexSearch operator in row 2 confirms that Hybrid Plan is active. Row 1 shows the IMCI execution context; row 4 confirms the InnoDB table scan for full row retrieval.

Performance test

The following test uses the OnTime dataset from ClickHouse. The OnTime table has 109 columns, making it a representative wide table. The table schema was modified to include an explicit primary key. The modified schema is as follows:

CREATE TABLE `ontime` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Year` year(4) DEFAULT NULL,
  `Quarter` tinyint(4) DEFAULT NULL,
  `Month` tinyint(4) DEFAULT NULL,
  `DayofMonth` tinyint(4) DEFAULT NULL,
  `DayOfWeek` tinyint(4) DEFAULT NULL,
  `FlightDate` date DEFAULT NULL,
  `UniqueCarrier` char(7) DEFAULT NULL,
  `AirlineID` int(11) DEFAULT NULL,
  `Carrier` char(2) DEFAULT NULL,
  `TailNum` varchar(50) DEFAULT NULL,
  `FlightNum` varchar(10) DEFAULT NULL,
  `OriginAirportID` int(11) DEFAULT NULL,
  `OriginAirportSeqID` int(11) DEFAULT NULL,
  `OriginCityMarketID` int(11) DEFAULT NULL,
  `Origin` char(5) DEFAULT NULL,
  `OriginCityName` varchar(100) DEFAULT NULL,
  `OriginState` char(2) DEFAULT NULL,
  `OriginStateFips` varchar(10) DEFAULT NULL,
  `OriginStateName` varchar(100) DEFAULT NULL,
  `OriginWac` int(11) DEFAULT NULL,
  `DestAirportID` int(11) DEFAULT NULL,
  `DestAirportSeqID` int(11) DEFAULT NULL,
  `DestCityMarketID` int(11) DEFAULT NULL,
  `Dest` char(5) DEFAULT NULL,
  `DestCityName` varchar(100) DEFAULT NULL,
  `DestState` char(2) DEFAULT NULL,
  `DestStateFips` varchar(10) DEFAULT NULL,
  `DestStateName` varchar(100) DEFAULT NULL,
  `DestWac` int(11) DEFAULT NULL,
  `CRSDepTime` int(11) DEFAULT NULL,
  `DepTime` int(11) DEFAULT NULL,
  `DepDelay` int(11) DEFAULT NULL,
  `DepDelayMinutes` int(11) DEFAULT NULL,
  `DepDel15` int(11) DEFAULT NULL,
  `DepartureDelayGroups` int(11) DEFAULT NULL,
  `DepTimeBlk` varchar(20) DEFAULT NULL,
  `TaxiOut` int(11) DEFAULT NULL,
  `WheelsOff` int(11) DEFAULT NULL,
  `WheelsOn` int(11) DEFAULT NULL,
  `TaxiIn` int(11) DEFAULT NULL,
  `CRSArrTime` int(11) DEFAULT NULL,
  `ArrTime` int(11) DEFAULT NULL,
  `ArrDelay` int(11) DEFAULT NULL,
  `ArrDelayMinutes` int(11) DEFAULT NULL,
  `ArrDel15` int(11) DEFAULT NULL,
  `ArrivalDelayGroups` int(11) DEFAULT NULL,
  `ArrTimeBlk` varchar(20) DEFAULT NULL,
  `Cancelled` tinyint(4) DEFAULT NULL,
  `CancellationCode` char(1) DEFAULT NULL,
  `Diverted` tinyint(4) DEFAULT NULL,
  `CRSElapsedTime` int(11) DEFAULT NULL,
  `ActualElapsedTime` int(11) DEFAULT NULL,
  `AirTime` int(11) DEFAULT NULL,
  `Flights` int(11) DEFAULT NULL,
  `Distance` int(11) DEFAULT NULL,
  `DistanceGroup` tinyint(4) DEFAULT NULL,
  `CarrierDelay` int(11) DEFAULT NULL,
  `WeatherDelay` int(11) DEFAULT NULL,
  `NASDelay` int(11) DEFAULT NULL,
  `SecurityDelay` int(11) DEFAULT NULL,
  `LateAircraftDelay` int(11) DEFAULT NULL,
  `FirstDepTime` varchar(10) DEFAULT NULL,
  `TotalAddGTime` varchar(10) DEFAULT NULL,
  `LongestAddGTime` varchar(10) DEFAULT NULL,
  `DivAirportLandings` varchar(10) DEFAULT NULL,
  `DivReachedDest` varchar(10) DEFAULT NULL,
  `DivActualElapsedTime` varchar(10) DEFAULT NULL,
  `DivArrDelay` varchar(10) DEFAULT NULL,
  `DivDistance` varchar(10) DEFAULT NULL,
  `Div1Airport` varchar(10) DEFAULT NULL,
  `Div1AirportID` int(11) DEFAULT NULL,
  `Div1AirportSeqID` int(11) DEFAULT NULL,
  `Div1WheelsOn` varchar(10) DEFAULT NULL,
  `Div1TotalGTime` varchar(10) DEFAULT NULL,
  `Div1LongestGTime` varchar(10) DEFAULT NULL,
  `Div1WheelsOff` varchar(10) DEFAULT NULL,
  `Div1TailNum` varchar(10) DEFAULT NULL,
  `Div2Airport` varchar(10) DEFAULT NULL,
  `Div2AirportID` int(11) DEFAULT NULL,
  `Div2AirportSeqID` int(11) DEFAULT NULL,
  `Div2WheelsOn` varchar(10) DEFAULT NULL,
  `Div2TotalGTime` varchar(10) DEFAULT NULL,
  `Div2LongestGTime` varchar(10) DEFAULT NULL,
  `Div2WheelsOff` varchar(10) DEFAULT NULL,
  `Div2TailNum` varchar(10) DEFAULT NULL,
  `Div3Airport` varchar(10) DEFAULT NULL,
  `Div3AirportID` int(11) DEFAULT NULL,
  `Div3AirportSeqID` int(11) DEFAULT NULL,
  `Div3WheelsOn` varchar(10) DEFAULT NULL,
  `Div3TotalGTime` varchar(10) DEFAULT NULL,
  `Div3LongestGTime` varchar(10) DEFAULT NULL,
  `Div3WheelsOff` varchar(10) DEFAULT NULL,
  `Div3TailNum` varchar(10) DEFAULT NULL,
  `Div4Airport` varchar(10) DEFAULT NULL,
  `Div4AirportID` int(11) DEFAULT NULL,
  `Div4AirportSeqID` int(11) DEFAULT NULL,
  `Div4WheelsOn` varchar(10) DEFAULT NULL,
  `Div4TotalGTime` varchar(10) DEFAULT NULL,
  `Div4LongestGTime` varchar(10) DEFAULT NULL,
  `Div4WheelsOff` varchar(10) DEFAULT NULL,
  `Div4TailNum` varchar(10) DEFAULT NULL,
  `Div5Airport` varchar(10) DEFAULT NULL,
  `Div5AirportID` int(11) DEFAULT NULL,
  `Div5AirportSeqID` int(11) DEFAULT NULL,
  `Div5WheelsOn` varchar(10) DEFAULT NULL,
  `Div5TotalGTime` varchar(10) DEFAULT NULL,
  `Div5LongestGTime` varchar(10) DEFAULT NULL,
  `Div5WheelsOff` varchar(10) DEFAULT NULL,
  `Div5TailNum` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=58592318 DEFAULT CHARSET=latin1 COMMENT='columnar=1'

Test query:

SELECT * FROM ontime ORDER BY ArrTime LIMIT 1000;

All queries ran from a cold start.

Execution strategy	Query time
Row store only	232.48 seconds
Columnstore index (IMCI) only	2.56 seconds
Hybrid Plan	0.33 seconds

Hybrid Plan is 7.8x faster than columnstore-only and 704x faster than row-store-only for this workload.