PolarDB:Best practices for using the IMCI feature

Step 1: Establish a baseline

Run the following queries against the lineitem table before creating any IMCI and record the execution times.

Single-table scan with filter

SELECT * FROM lineitem WHERE L_COMMENT > 'aaaaaaaa' AND L_COMMENT < 'aaaaaaz';
-- Sample result
Empty set (8 min 47.29 sec)

Single-column aggregation

SELECT SUM(L_DISCOUNT) FROM lineitem;
-- Sample result
+-----------------+
| SUM(L_DISCOUNT) |
+-----------------+
|     30001636.44 |
+-----------------+
1 row in set (2 min 6.64 sec)

Grouped aggregation

SELECT AVG(L_DISCOUNT) FROM lineitem
WHERE L_SHIPDATE <= date '1998-12-01' - interval '90' day
GROUP BY L_RETURNFLAG, L_LINESTATUS;
-- Sample result: 4 rows in set (6 min 28.96 sec)

Pagination with ORDER BY and LIMIT

SELECT L_ORDERKEY, SUM(L_QUANTITY) FROM lineitem
GROUP BY L_ORDERKEY
ORDER BY SUM(L_QUANTITY) DESC
LIMIT 1000000, 100;
-- Sample result: 100 rows in set (12 min 24.22 sec)

Step 2: Create an IMCI on the lineitem table

ALTER TABLE lineitem COMMENT 'COLUMNAR=1 lineitem';
-- Sample result
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

For additional options, see Create an IMCI.

Step 3: Wait for the IMCI to finish building

Query INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS to monitor build progress.

SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;

Key fields in the output:

Sample output while building:

| SCHEMA_NAME | TABLE_NAME | STATUS   | APPROXIMATE_ROWS | SCANNED_ROWS | ESTIMATE_SECOND |
| tpch        | lineitem   | Building | 590446240        | 36718757(6%) | 299             |

Sample output when complete:

| SCHEMA_NAME | TABLE_NAME | FINISHED_AT         | STATUS       | APPROXIMATE_ROWS | SCANNED_ROWS    |
| tpch        | lineitem   | 2024-10-21 13:50:11 | Safe to read | 590446240        | 600037902(100%) |

For more information, see View the building progress of IMCIs.

Step 4: Re-run the baseline queries and compare

Run the same four queries from Step 1. Sample results with IMCI:

These results are benchmarks, not guarantees. Actual execution times vary with cluster configuration, connection count, query concurrency, and system load.

Step 1: Establish a baseline

At this point, only lineitem has an IMCI. Run the following queries and record the execution times. Queries that join tables without IMCIs do not benefit from columnar acceleration.

To check whether your query is fully covered by IMCIs before running it, call dbms_imci.check_columnar_index('<query_string>'). The stored procedure identifies tables and columns not covered by IMCIs. See Check whether an IMCI is created for a table in an SQL statement and DDL tools for IMCIs.

Multi-table join

SELECT COUNT(l3.L_DISCOUNT)
FROM (
  (
    (
      (
        (nation n1 STRAIGHT_JOIN nation n2 ON n1.N_NATIONKEY = n2.N_NATIONKEY)
        STRAIGHT_JOIN supplier ON n2.N_NATIONKEY = supplier.S_NATIONKEY AND S_SUPPKEY < 2000
      )
      STRAIGHT_JOIN lineitem AS l1 ON l1.L_SUPPKEY = supplier.S_SUPPKEY
    )
    STRAIGHT_JOIN lineitem AS l2 ON l1.L_ORDERKEY = l2.L_ORDERKEY AND l1.L_LINENUMBER = l2.L_LINENUMBER
  )
  STRAIGHT_JOIN lineitem AS l3 ON l2.L_ORDERKEY = l3.L_ORDERKEY AND l2.L_LINENUMBER = l3.L_LINENUMBER
)
GROUP BY n1.N_NAME;
-- Result: query stopped after 7,200 seconds (execution timeout)

Query with a subquery

SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT
FROM orders
WHERE O_ORDERDATE >= '1995-01-01'
  AND O_ORDERDATE < date_add('1995-01-01', interval '3' month)
  AND EXISTS (
    SELECT * FROM lineitem
    WHERE L_ORDERKEY = O_ORDERKEY AND L_COMMITDATE < L_RECEIPTDATE
  )
GROUP BY O_ORDERPRIORITY
ORDER BY O_ORDERPRIORITY;
-- Sample result: 5 rows in set (4 min 9.51 sec)

Multi-table join with subqueries

SELECT C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE, SUM(L_QUANTITY)
FROM (
  SELECT * FROM orders
  WHERE O_ORDERKEY IN (
    SELECT L_ORDERKEY FROM lineitem GROUP BY L_ORDERKEY HAVING SUM(L_QUANTITY) > 300
  )
) AS tmp, customer, lineitem
WHERE C_CUSTKEY = O_CUSTKEY AND O_ORDERKEY = L_ORDERKEY
GROUP BY C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE
ORDER BY O_TOTALPRICE DESC, O_ORDERDATE;
-- Sample result: 6,398 rows in set (12 min 46.15 sec)

Step 2: Create IMCIs for all tables in the tpch database

Use the batch DDL command to create IMCIs for all tables at once:

CREATE COLUMNAR INDEX FOR TABLES IN tpch;
-- Sample output
+------------+-------------------+
| Table_Name | Result            |
+------------+-------------------+
| customer   | Ok                |
| lineitem   | Skip by no change |
| nation     | Ok                |
| orders     | Ok                |
| part       | Ok                |
| partsupp   | Ok                |
| region     | Ok                |
| supplier   | Ok                |
+------------+-------------------+
8 rows in set (56.74 sec)

lineitem shows Skip by no change because an IMCI already exists for it.

Step 3: Wait for all IMCIs to finish building

SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;

Wait until all tables show Safe to read in the STATUS field. Sample output when all IMCIs are ready:

| SCHEMA_NAME | TABLE_NAME | FINISHED_AT         | STATUS       | APPROXIMATE_ROWS | SCANNED_ROWS    |
| tpch        | region     | 2024-10-21 14:43:27 | Safe to read | 5                | 5(100%)         |
| tpch        | lineitem   | 2024-10-21 14:42:23 | Safe to read | 590446240        | 600037902(100%) |
| tpch        | supplier   | 2024-10-21 14:44:17 | Safe to read | 986923           | 1000000(100%)   |
| tpch        | part       | 2024-10-21 14:43:38 | Safe to read | 19354445         | 20000000(100%)  |
| tpch        | customer   | 2024-10-21 14:43:27 | Safe to read | 13179406         | 15000000(100%)  |
| tpch        | nation     | 2024-10-21 14:43:27 | Safe to read | 25               | 25(100%)        |
| tpch        | partsupp   | 2024-10-21 14:44:16 | Safe to read | 67862725         | 80000000(100%)  |
| tpch        | orders     | 2024-10-21 14:44:27 | Safe to read | 142929780        | 150000000(100%) |

Step 4: Re-run the queries and compare

Run the same three queries from Step 1. Sample results with full IMCI coverage:

These results are benchmarks, not guarantees. Actual execution times vary with cluster configuration, connection count, query concurrency, and system load.

Step 1: Verify the imci_enable_pack_order_key parameter

The imci_enable_pack_order_key parameter controls IMCI data sorting. Its default value is ON. If you have not changed it, skip this step.

In the PolarDB console, all parameters use the loose_ prefix. To modify this parameter in the console, use loose_imci_enable_pack_order_key. For instructions, see Configure cluster and node parameters.

Step 2: Run a baseline query without a sort key

SELECT
  L_SHIPMODE,
  SUM(CASE WHEN O_ORDERPRIORITY = '1-URGENT' OR O_ORDERPRIORITY = '2-HIGH' THEN 1 ELSE 0 END) AS high_line_count,
  SUM(CASE WHEN O_ORDERPRIORITY <> '1-URGENT' AND O_ORDERPRIORITY <> '2-HIGH' THEN 1 ELSE 0 END) AS low_line_count
FROM orders, lineitem
WHERE O_ORDERKEY = L_ORDERKEY
  AND L_SHIPMODE IN ('MAIL', 'SHIP')
  AND L_COMMITDATE < L_RECEIPTDATE
  AND L_SHIPDATE < L_COMMITDATE
  AND L_RECEIPTDATE >= date '1994-01-01'
  AND L_RECEIPTDATE < date '1994-01-01' + interval '1' year
GROUP BY L_SHIPMODE
ORDER BY L_SHIPMODE;
-- Sample result: 2 rows in set (4.35 sec)

Step 3: Add a sort key to the lineitem table

ALTER TABLE lineitem COMMENT='COLUMNAR=1 order_key=L_RECEIPTDATE,L_SHIPMODE';

Step 4: Wait for the sorted IMCI to finish building

Monitor progress with SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS until lineitem shows Safe to read. For estimated build time, see Time consumption.

Step 5: Re-run the query and compare

+------------+-----------------+----------------+
| L_SHIPMODE | high_line_count | low_line_count |
+------------+-----------------+----------------+
| MAIL       |          623115 |         934713 |
| SHIP       |          622979 |         934534 |
+------------+-----------------+----------------+
2 rows in set (0.88 sec)

These results are benchmarks, not guarantees. Actual execution times vary with cluster configuration, connection count, query concurrency, and system load.

Why doesn't my query use IMCI?

IMCI acceleration requires four conditions: a read-only IMCI node exists in the cluster, all tables and columns in the query are covered by IMCIs, the estimated execution cost exceeds the configured threshold, and the query is routed to the IMCI node. Work through the following checks to identify the cause.

1. Check whether the query is routed to the IMCI node.

Verify that the IMCI node is included in the selected nodes of the cluster endpoint and that Transactional/Analytical Processing Splitting is enabled. Use SQL Explorer to confirm which node ran the query. For instructions, see SQL Explorer.

To force a query to the IMCI node for testing, prefix it with the /*FORCE_IMCI_NODES*/ hint:

/*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;

Alternatively, create a custom endpoint associated only with the IMCI node. For details, see HTAP-based request distribution among row store and IMCI nodes.

2. Check whether the estimated execution cost meets the threshold.

On the IMCI node, the optimizer uses IMCI only when the estimated cost exceeds the threshold set by loose_imci_ap_threshold (or loose_cost_threshold_for_imci in engine versions earlier than 8.0.1.1.39 or 8.0.2.2.23). Run EXPLAIN and then check the estimated cost:

EXPLAIN SELECT * FROM t1;
/*ROUTE_TO_LAST_USED*/SHOW STATUS LIKE 'Last_query_cost';

Use the /*ROUTE_TO_LAST_USED*/ hint with SHOW STATUS LIKE 'Last_query_cost' when connected through the cluster endpoint to make sure the cost is retrieved from the correct node.

If the estimated cost is below the threshold, lower the threshold or override it for a single query:

/*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

3. Check whether all columns in the query are covered by an IMCI.

Call the built-in stored procedure to check coverage:

CALL dbms_imci.check_columnar_index('SELECT COUNT(*) FROM t1 WHERE t1.a > 1');

If the procedure returns uncovered tables or columns, create IMCIs for them. If the result is empty, full coverage is confirmed.

4. Check IMCI usage limits.

Some SQL constructs are not supported by IMCI. Review IMCI usage limits to confirm the query is eligible.

If the query still does not use IMCI after these checks, contact us or join DingTalk group 27520023189 for expert support.

How do I create the right IMCI for a query?

An IMCI accelerates a query only when it covers all columns referenced in the query. Use the dbms_imci.columnar_advise() stored procedure to get the exact DDL statement needed:

CALL dbms_imci.columnar_advise('<your_query_string>');

For a batch of queries, use dbms_imci.columnar_advise_begin(), dbms_imci.columnar_advise(), and dbms_imci.columnar_advise_end() together. For details, see Batch obtain the DDL statements used to create IMCIs and Obtain the DDL statement used to create an IMCI.