Use EXPLAIN to view DN execution plans

How it works

PolarDB-X has two node types: compute nodes (CNs) and data nodes (DNs). When a query arrives, the CN parses it, generates a logical execution plan, and dispatches physical SQL to the DNs. Each DN then builds its own execution plan—the DN execution plan—based on local statistics and data distribution.

Because DN execution plans are generated independently, they can differ across shards even for the same query. These differences are the most common cause of slow queries in PolarDB-X.

Choose a command

Start with EXPLAIN EXECUTE to get an overview, then drill down as needed.

Prerequisites

Before you begin, ensure that you have:

• An instance running version polardb-2.5.0_5.4.20-20250618_xcluster8.4.20-20250612 or later

Run a quick diagnosis with EXPLAIN EXECUTE

EXPLAIN EXECUTE is the starting point. It sends all physical SQL statements to their corresponding physical tables on each DN, collects the resulting execution plans, and compares them. The output format mirrors the native MySQL EXPLAIN output, with two additional fields in the Extra column:

Example:

mysql> EXPLAIN EXECUTE SELECT col1 FROM exe_tb WHERE col1 < 6\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: exe_tb
   partitions: NULL
         type: index
possible_keys: auto_shard_key_col1
          key: auto_shard_key_col1
      key_len: 5
          ref: NULL
         rows: 2000
     filtered: 100
        Extra: Different plan(exe_tb_58Db_00001,exe_tb_58Db_00015); Scan rows(1, 66819); Using where; Using index

Reading the Extra column:

• Different plan(exe_tb_58Db_00001,exe_tb_58Db_00015) — These two shards use different execution plans. All other shards share the same plan.

• Scan rows(1, 66819) — Estimated scan row counts range from 1 to 66,819 across shards. This gap indicates probable data skew.

Next step: Use EXPLAIN DIFF_EXECUTE to see exactly how the plans differ.

Locate plan differences with EXPLAIN DIFF_EXECUTE

EXPLAIN DIFF_EXECUTE lists only the physical tables whose execution plans differ from the majority. The table column shows the physical table name for each row.

mysql> EXPLAIN DIFF_EXECUTE SELECT col1 FROM exe_tb WHERE col1 < 6\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: exe_tb_58Db_00001
         type: index
possible_keys: auto_shard_key_col1
          key: auto_shard_key_col1
      key_len: 5
          ref: NULL
         rows: 2000
     filtered: 100
        Extra: Different plan(exe_tb_58Db_00001,exe_tb_58Db_00015); Scan rows(1, 44939); Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: exe_tb_58Db_00015
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1009
     filtered: 33.32999801635742
        Extra: Using where

Comparing the two rows:

• exe_tb_58Db_00001 uses type: index (index scan) — efficient.

• exe_tb_58Db_00015 uses type: ALL (full table scan) with no index selected — inefficient.

The shard exe_tb_58Db_00015 is the problematic one. Its statistics likely differ from the other shards, causing the optimizer to skip the index.

View all plans with EXPLAIN ALL_EXECUTE

EXPLAIN ALL_EXECUTE lists the execution plan for every physical table. Use this when you need a complete audit rather than just the outliers.

mysql> EXPLAIN ALL_EXECUTE SELECT col1 FROM exe_tb WHERE col1 < 6\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: exe_tb_58Db_00001
   partitions: NULL
         type: index
possible_keys: auto_shard_key_col1
          key: auto_shard_key_col1
      key_len: 5
          ref: NULL
         rows: 2000
     filtered: 100
        Extra: Different plan(exe_tb_58Db_00001,exe_tb_58Db_00015); Scan rows(1, 44939); Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: exe_tb_58Db_00004
   partitions: NULL
         type: range
possible_keys: auto_shard_key_col1
          key: auto_shard_key_col1
      key_len: 5
          ref: NULL
         rows: 44939
     filtered: 100
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: exe_tb_58Db_00005
   partitions: NULL
         type: index
possible_keys: auto_shard_key_col1
          key: auto_shard_key_col1
      key_len: 5
          ref: NULL
         rows: 1
     filtered: 100
        Extra: Using where; Using index
************** Execution plans for other physical tables are omitted here... ********************

Advanced analysis

For cost analysis and deep performance investigation, PolarDB-X supports three MySQL 8.0-compatible output formats. All three display a TABLE column with the physical table name and a PHYSICAL_PLAN column with the plan details.

mysql> EXPLAIN TREE_EXECUTE SELECT col1 FROM exe_tb WHERE col1 < 6;
+------------------------+------------------------------------------------------------+
| TABLE                  | PHYSICAL_PLAN                                              |
+------------------------+------------------------------------------------------------+
| [[exe_tb_58Db_00001]]
 | -> Filter: (exe_tb.col1 < 6)  (cost=202.75 rows=2000)
    -> Index scan on exe_tb using auto_shard_key_col1  (cost=202.75 rows=2000)
 |
+------------------------+------------------------------------------------------------+

mysql> EXPLAIN JSON_EXECUTE SELECT col1 FROM exe_tb WHERE col1 < 6;
+------------------------+------------------------------------------+
| TABLE                  | PHYSICAL_PLAN                            |
+------------------------+------------------------------------------+
| [[exe_tb_58Db_00001]]
 | {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "202.75"
    },
    "table": {
      "table_name": "exe_tb",
      "access_type": "index",
      "possible_keys": [
        "auto_shard_key_col1"
      ],
      "key": "auto_shard_key_col1",
      "used_key_parts": [
        "col1"
      ],
      "key_length": "5",
      "rows_examined_per_scan": 2000,
      "rows_produced_per_join": 2000,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "2.75",
        "eval_cost": "200.00",
        "prefix_cost": "202.75",
        "data_read_per_join": "31K"
      },
      "used_columns": [
        "col1"
      ],
      "attached_condition": "(`db0_p00000`.`exe_tb`.`col1` < 6)"
    }
  }
} |
+------------------------+------------------------------------------+

mysql> EXPLAIN ANALYZE_EXECUTE SELECT col1 FROM exe_tb WHERE col1 < 6;
+------------------------+-----------------------------------------------------------------------------------------------------------+
| TABLE                  | PHYSICAL_PLAN                                                                                             |
+------------------------+-----------------------------------------------------------------------------------------------------------+
| [[exe_tb_58Db_00001]]
 | -> Filter: (exe_tb.col1 < 6)  (cost=202.75 rows=2000) (actual time=0.028..0.655 rows=2000 loops=1)
    -> Covering index scan on exe_tb using auto_shard_key_col1  (cost=202.75 rows=2000) (actual time=0.026..0.468 rows=2000 loops=1)
 |
+------------------------+-----------------------------------------------------------------------------------------------------------+

FAQ

Which command should I start with?

Start with EXPLAIN EXECUTE. If Different plan appears in the Extra column, run EXPLAIN DIFF_EXECUTE to isolate the diverging shards. For cost analysis or actual execution timing on a specific shard, use EXPLAIN TREE_EXECUTE or EXPLAIN ANALYZE_EXECUTE. To audit all shards at once, use EXPLAIN ALL_EXECUTE.