Use EXPLAIN to analyze the execution plan of an SQL statement - PolarDB

This topic describes how to check whether a query can use the In-Memory Column Index (IMCI) feature.

Method

An IMCI can be used to query only the columns that it covers. If the columns that you want to query are covered by IMCIs, you can use the IMCI feature to accelerate your queries.
Note
For more information about how to check whether an IMCI is valid for the columns that you want to query, see Check whether an IMCI is created for a table in an SQL statement.

In PolarDB for MySQL, a column store node execution plan is displayed in the horizontal tree format, which is different from a row store node execution plan that is displayed as a chart. This way, you can easily distinguish the two types of execution plans. Example:

Note

In this example, a test based on the TPC-H benchmark is implemented, but it does not meet all the requirements of a TPC-H benchmark test. Therefore, the test results are incomparable with the published results of the TPC-H benchmark test.

Execute an EXPLAIN statement to view the execution plan of a query.

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\G

If the row store node execution plan is used, the following result is returned:

+----+-------------+----------+------------+--------+---------------+---------+---------+--------------------------+---------+----------+----------------------------------------------+
| id | select_type | table    | partitions | type   | possible_keys | key     | key_len | ref                      | rows    | filtered | Extra                                        |
+----+-------------+----------+------------+--------+---------------+---------+---------+--------------------------+---------+----------+----------------------------------------------+
|  1 | SIMPLE      | orders   | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                     | 1482516 |    33.33 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | customer | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | tpch1g.orders.O_CUSTKEY  |       1 |    10.00 | Using where                                  |
|  1 | SIMPLE      | lineitem | NULL       | ref    | PRIMARY       | PRIMARY | 8       | tpch1g.orders.O_ORDERKEY |       3 |    33.33 | Using where                                  |
+----+-------------+----------+------------+--------+---------------+---------+---------+--------------------------+---------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

If the column store node execution plan is used, the following result is returned:

*************************** 1. row ***************************
IMCI Execution Plan (max_dop = 8, max_query_mem = 3435134976):
Project | Exprs: temp_table3.lineitem.L_ORDERKEY, temp_table3.SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT), temp_table3.orders.O_ORDERDATE, temp_table3.orders.O_SHIPPRIORITY
  Sort | Exprs: temp_table3.SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT) DESC,temp_table3.orders.O_ORDERDATE ASC
    HashGroupby | OutputTable(3): temp_table3 | Grouping: lineitem.L_ORDERKEY orders.O_ORDERDATE orders.O_SHIPPRIORITY | Output Grouping: lineitem.L_ORDERKEY, orders.O_ORDERDATE, orders.O_SHIPPRIORITY | Aggrs: SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT)
      HashJoin | HashMode: DYNAMIC | JoinMode: INNER | JoinPred: orders.O_ORDERKEY = lineitem.L_ORDERKEY
        HashJoin | HashMode: DYNAMIC | JoinMode: INNER | JoinPred: orders.O_CUSTKEY = customer.C_CUSTKEY
          CTableScan | InputTable(0): orders | Pred: (orders.O_ORDERDATE < 03/24/1995 00:00:00.000000)
          CTableScan | InputTable(1): customer | Pred: (customer.C_MKTSEGMENT = "BUILDING")
        CTableScan | InputTable(2): lineitem | Pred: (lineitem.L_SHIPDATE > 03/24/1995 00:00:00.000000)
1 row in set (0.04 sec)

FAQ

Why is the execution plan of a query not a column store node execution plan?

The queried data columns may not be covered by an IMCI. In this case, you must create an IMCI for the data columns. For information about how to create IMCIs, see Execute the CREATE TABLE statement to create an IMCI or Execute DDL statements to dynamically create and delete an IMCI.
If automatic request distribution among row store and column store nodes is enabled for a cluster endpoint, the value of the loose_imci_ap_threshold parameter may not meet the requirement. The SHOW STATUS LIKE 'Last_query_cost' value must be greater than the SHOW VARIABLES LIKE 'imci_ap_threshold' value. If this requirement is not met, go to the Parameters page for your PolarDB cluster in the PolarDB console, and modify the value of the loose_imci_ap_threshold parameter. For more information, see the "Step 2: Set the thresholds for automatic request distribution" section of the Automatic request distribution among row store and column store nodes topic.
If your application is connected to a read-only column store node, the value of the loose_cost_threshold_for_imci parameter may not meet the requirement. The SHOW STATUS LIKE 'Last_query_cost' value must be greater than the SHOW VARIABLES LIKE 'cost_threshold_for_imci' value. If this requirement is not met, go to the Parameters page for your PolarDB cluster in the PolarDB console, and modify the value of the loose_cost_threshold_for_imci parameter. For more information, see the "Step 2: Set the thresholds for automatic request distribution" section of the Automatic request distribution among row store and column store nodes topic.
Your application may have been connected to the primary node, a column store node, or a cluster endpoint for which the automatic request distribution among row store and column store nodes feature is disabled. In this case, connect your application to a cluster endpoint for which the automatic request distribution among row store and column store nodes feature is enabled, or connect your application to a read-only column store node.