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

This topic describes how a Hybrid Plan accelerates wide table queries. It covers the underlying principles, applicable scenarios, limits, and performance test results.

How it works

When you query a large wide table, you might filter on a few columns using `WHERE` or `JOIN` clauses, or query the `TOP K` rows, and then output all the details for the selected rows. For these types of queries, using a columnstore index is effective for the initial filtering. However, when you fetch all the detailed columns for the filtered rows, using only a columnstore index causes a read amplification problem. In this situation, using a row store index to fetch the full row details is more efficient. A Hybrid Plan is a query method that uses both a columnstore index and a row store index in the same SQL statement.

A Hybrid Plan speeds up wide table queries. For parts of the execution plan suited for columnstore processing, the optimizer uses an In-Memory Column Index (IMCI) to retrieve intermediate results. These results contain only primary key information. Then, the system uses these primary keys with the InnoDB primary index to look up and output all the projected columns. The figure above illustrates this process.

Scenarios

Wide table queries that return 15 or more columns.

Applicability

Your cluster must be an Enterprise Edition cluster that runs one of the following 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.

Limits

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

Note

If a Hybrid Plan fails to accelerate a slow SQL statement due to limits, such as the table type, you can contact us for help.

Parameter description

To enable the Hybrid Plan feature, set the imci_enable_hybrid_plan parameter to ON. After you enable this feature, the optimizer automatically uses a Hybrid Plan for suitable queries. To force a specific query to use a Hybrid Plan, add a hint to the SQL statement that sets the force_hybrid_index_search option in the imci_optimizer_switch parameter to ON.

Parameter Name

Description

imci_enable_hybrid_plan

Controls the Hybrid Plan feature. Valid values:

ON (Default): Enables the feature.
OFF: Disables the Hybrid Plan feature that accelerates queries on wide tables.

imci_optimizer_switch

Specifies whether to use Hybrid Plan for a query. Valid values:

'force_hybrid_index_search=OFF' (Default): The optimizer automatically determines whether to use Hybrid Plan.
'force_hybrid_index_search=ON': Forces the query to use Hybrid Plan.

Usage notes

This section uses SELECT * FROM t1; as an example to show how to use a Hybrid Plan to accelerate wide table queries. Perform the following steps:

Connect to the PolarDB database. For more information, see Connect to a database cluster.
Set the imci_enable_hybrid_plan parameter to ON to enable the Hybrid Plan feature. The optimizer then automatically uses a Hybrid Plan for suitable queries.
```
SET imci_enable_hybrid_plan=ON;
```

To force a specific query to use a Hybrid Plan, add a hint to the SQL statement. In the hint, set the force_hybrid_index_search option of the imci_optimizer_switch parameter to ON.

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

Use the EXPLAIN statement to check if a Hybrid Plan is active for the SQL statement. If the execution plan includes HybridIndexSearch, this indicates that the feature is active.

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

The following result is returned:

+----+-------------------------+------+---------------------------------------------------------------+
| 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)

Performance test

This test uses the OnTime dataset from ClickHouse to compare query performance across three scenarios: using only a row store index, using only an In-Memory Column Index (IMCI), and using a Hybrid Plan.

The table in the OnTime dataset is a typical large wide table with 109 columns. For this test, the table definition from ClickHouse was modified to explicitly define a primary key. The modified table 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'

The test uses the following SQL statement:

SELECT * FROM ontime ORDER BY ArrTime LIMIT 1000;

The SQL statement is executed in each of the three scenarios. All queries are performed from a cold start. The query times are as follows:

Row store only	Columnstore indexes only	Accelerate column store wide table queries with Hybrid Plan
232.48 seconds	2.56 seconds	0.33 seconds

The results in the table show that a Hybrid Plan significantly improves performance for wide table queries.