Run EXPLAIN on a SQL query to check whether PolarDB uses in-memory column indexes (IMCIs) to accelerate it. IMCI-accelerated queries produce a distinct execution plan format that you can identify immediately.
Prerequisites
Before you begin, ensure that you have:
A read-only column store node added to your PolarDB for MySQL cluster
IMCIs created on the tables referenced in your queries
A cluster endpoint with Transactional/Analytical Processing Splitting enabled
How to read IMCI execution plans
When a query runs on the column store node using IMCIs, the EXPLAIN output switches from the standard MySQL table format to a horizontal tree format.
Key signal: Look for IMCI Execution Plan in the Extra Info column of the first row. If that string is present, the query is accelerated by IMCIs. If the output uses the standard table format without this header, the query is running on row store indexes.
SQL query used in the examples below:
EXPLAIN SELECT l_orderkey, SUM(l_extendedprice * (1 - l_discount)) AS revenue, o_orderdate, o_shippriority
FROM customer, orders, lineitem
WHERE c_mktsegment = 'BUILDING'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < date '1995-03-24'
AND l_shipdate > date '1995-03-24'
GROUP BY l_orderkey, o_orderdate, o_shippriority
ORDER BY revenue DESC, o_orderdate;Row store execution plan — standard MySQL table format, no IMCI acceleration:
+----+-------------+----------+------------+------+--------------------+------------+---------+-----------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------+------------+---------+-----------------------------+--------+----------+----------------------------------------------+
| 1 | SIMPLE | customer | NULL | ALL | PRIMARY | NULL | NULL | NULL | 147630 | 10.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | orders | NULL | ref | PRIMARY,ORDERS_FK1 | ORDERS_FK1 | 4 | tpch100g.customer.C_CUSTKEY | 14 | 33.33 | Using where |
| 1 | SIMPLE | lineitem | NULL | ref | PRIMARY | PRIMARY | 4 | tpch100g.orders.O_ORDERKEY | 4 | 33.33 | Using where |
+----+-------------+----------+------------+------+--------------------+------------+---------+-----------------------------+--------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)Column store execution plan — horizontal tree format, IMCI acceleration confirmed:
+----+----------------------------+----------+--------+--------+-----------------------------------------------------------------------------+
| ID | Operator | Name | E-Rows | E-Cost | Extra Info |
+----+----------------------------+----------+--------+--------+-----------------------------------------------------------------------------+
| 1 | Select Statement | | | | IMCI Execution Plan (max_dop = 4, max_query_mem = 858993459) |
| 2 | └─Sort | | | | Sort Key: revenue DESC,o_orderdate ASC |
| 3 | └─Hash Groupby | | | | Group Key: (lineitem.L_ORDERKEY, orders.O_ORDERDATE, orders.O_SHIPPRIORITY) |
| 4 | └─Hash Join | | | | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY |
| 5 | ├─Hash Join | | | | Join Cond: customer.C_CUSTKEY = orders.O_CUSTKEY |
| 6 | │ ├─Table Scan | customer | | | Cond: (C_MKTSEGMENT = "BUILDING") |
| 7 | │ └─Table Scan | orders | | | Cond: (O_ORDERDATE < 03/24/1995) |
| 8 | └─Table Scan | lineitem | | | Cond: (L_SHIPDATE > 03/24/1995) |
+----+----------------------------+----------+--------+--------+-----------------------------------------------------------------------------+
8 rows in set (0.01 sec)Check IMCI coverage before running queries
An IMCI accelerates a query only when it covers all columns referenced in that query. If any table or column is not covered, the IMCI does not take effect.
To check whether the IMCIs in your cluster cover all columns in a SQL statement, call dbms_imci.check_columnar_index():
CALL dbms_imci.check_columnar_index('SELECT COUNT(*) FROM t1 WHERE t1.a > 1');If all columns are covered, the stored procedure returns an empty result set.
If any columns are not covered, the stored procedure returns the uncovered tables and columns. Create IMCIs for those columns before running the query.
To get the DDL statement for creating an IMCI that covers all columns in a query, call dbms_imci.columnar_advise():
dbms_imci.columnar_advise('<query_string>');For more information, see Check whether an IMCI is created for a table in a SQL statement and Obtain the DDL statement used to create an IMCI.
FAQ
Why doesn't my query use IMCIs?
A query uses IMCI acceleration only when all four conditions are met:
A read-only column store node is in the cluster
IMCIs exist on all tables referenced in the query
The query's estimated execution cost exceeds the configured threshold
The query is routed to the read-only column store node
Work through the following steps to identify which condition is not met.
Step 1: Verify the query reaches the column store node
Check whether the read-only column store node is listed in the selected nodes of the cluster endpoint. Use SQL Explorer to confirm that the query was actually routed to that node.
PolarProxy routes a query to the column store node only when:
The query connects through the cluster endpoint
Transactional/Analytical Processing Splitting is enabled for the cluster endpoint
The query's estimated cost exceeds the threshold set by
loose_imci_ap_threshold(orloose_cost_threshold_for_imciin older engine versions)
To force a query to the column store node for testing, add the /*FORCE_IMCI_NODES*/ hint before the SELECT keyword:
/*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;Alternatively, create a custom endpoint associated with the column store node to guarantee routing. For more information, see HTAP-based request distribution among row store and column store nodes.
loose_imci_ap_thresholdreplacesloose_cost_threshold_for_imciin engine minor versions 8.0.1.1.39 or later, or 8.0.2.2.23 or later.
Step 2: Check whether the query cost meets the threshold
On the column store node, the optimizer estimates the query's execution cost. If the estimate exceeds the threshold set by loose_imci_ap_threshold or cost_threshold_for_imci, the optimizer uses IMCIs. Otherwise, it falls back to row store indexes.
If EXPLAIN shows a row store plan even after the query reaches the column store node, check the estimated cost against the threshold:
-- View the execution plan
EXPLAIN SELECT * FROM t1;
-- Get the estimated cost of the previous query
SHOW STATUS LIKE 'Last_query_cost';If connecting through the cluster endpoint, add/*ROUTE_TO_LAST_USED*/to read the cost from the correct node:/*ROUTE_TO_LAST_USED*/SHOW STATUS LIKE 'Last_query_cost';
If the estimated cost is below the threshold, either adjust the loose_imci_ap_threshold or loose_cost_threshold_for_imci parameter, or override the threshold for a single query using a hint:
/*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;For more information, see Specify the thresholds for automatic request distribution.
Step 3: Verify IMCI column coverage
Call dbms_imci.check_columnar_index() to verify that the IMCIs cover all tables and columns in the query:
CALL dbms_imci.check_columnar_index('SELECT COUNT(*) FROM t1 WHERE t1.a > 1');If the stored procedure returns uncovered tables or columns, create IMCIs for them. If it returns an empty result set, all columns are covered.
Step 4: Check IMCI usage limits
Some query patterns are not supported by IMCIs. Review the IMCI usage limits to confirm the query is eligible.
If the query still does not use IMCIs after completing all four steps, see IMCI best practices or contact support.
How do I create the right IMCI for a query?
A query uses an IMCI only when the IMCI covers all columns referenced in the query. Use dbms_imci.columnar_advise() to generate the DDL statement for creating an IMCI that covers all columns in a given query:
dbms_imci.columnar_advise('<query_string>');To generate DDL statements for a batch of queries, use dbms_imci.columnar_advise_begin(), dbms_imci.columnar_advise(), and dbms_imci.columnar_advise_end() together. For more information, see Batch obtain the DDL statements used to create IMCIs.
If a query's columns are not fully covered, use ALTER TABLE to add the missing columns to an existing IMCI, or run the DDL statement from columnar_advise() to create a new one.