EXPLAIN|V4.1.0| docs|Distributed Database

EXPLAIN

Last Updated：2023-07-28 02:55:42 Updated

Purpose

You can use this statement to interpret the execution plan for an SQL statement, which can be a SELECT, DELETE, INSERT, REPLACE or UPDATE statement.

EXPLAIN, DESCRIBE, and DESC are synonyms.

Syntax

{EXPLAIN | DESCRIBE | DESC} [explain_type] dml_statement;

explain_type:
    BASIC
  | OUTLINE
  | EXTENDED
  | EXTENDED_NOADDR
  | PARTITIONS
  | FORMAT = {TRADITIONAL| JSON}

dml_statement:
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement

Parameters

Parameter	Description
BASIC	The basic information about the output plan, such as the operator ID, operator name, and name of the referenced table.
OUTLINE	The outline information contained in the output plan.
EXTENDED	The extra information generated by `EXPLAIN`, including the input and output columns of each operator, partition information about the access table, and the active filter. If the current operator uses an index, the used index columns and extracted query range are displayed.
EXTENDED_NOADDR	Specifies to display the brief extra information.
PARTITIONS	Specifies to display the partition-related information.
FORMAT = {TRADITIONAL\| JSON}	The output format of `EXPLAIN`: `TRADITIONAL`: presents the output in the tabular format. `JSON`: presents the output in the `KEY:VALUE` format. The output is displayed as JSON strings, including `EXTENDED` and `PARTITIONS` information.

Examples

Query the statement execution plan without specifying explain_type.

obclient> EXPLAIN SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR   |NAME|EST. ROWS|COST   |
---------------------------------------
|0 |HASH JOIN  |    |9801000  |5933109|
|1 | TABLE SCAN|t2  |10000    |6219   |
|2 | TABLE SCAN|t1  |100000   |68478  |
=======================================

Outputs & filters:
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil),
      equal_conds([t1.c2 = t2.c2]), other_conds(nil)
  1 - output([t2.c2], [t2.c1]), filter(nil),
      access([t2.c2], [t2.c1]), partitions(p0)
  2 - output([t1.c2], [t1.c1]), filter(nil),
      access([t1.c2], [t1.c1]), partitions(p0)

Use the EXTENDED_NOADDR keyword to output additional information.

obclient> EXPLAIN EXTENDED_NOADDR SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR   |NAME|EST. ROWS|COST   |
---------------------------------------
|0 |HASH JOIN  |    |9801000  |5933109|
|1 | TABLE SCAN|t2  |10000    |6219   |
|2 | TABLE SCAN|t1  |100000   |68478  |
=======================================

Outputs & filters:
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil),
      equal_conds([t1.c2 = t2.c2]), other_conds(nil)
  1 - output([t2.c2], [t2.c1]), filter(nil),
      access([t2.c2], [t2.c1]), partitions(p0),
      is_index_back=false,
      range_key([t2.c1]), range(4 ; MAX),
      range_cond([t2.c1 > 4])
  2 - output([t1.c2], [t1.c1]), filter(nil),
      access([t1.c2], [t1.c1]), partitions(p0),
      is_index_back=false,
      range_key([t1.__pk_increment], [t1.__pk_cluster_id], [t1.__pk_partition_id]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true

Show an execution plan in the TRADITIONAL format.

obclient> EXPLAIN FORMAT=TRADITIONAL SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR   |NAME|EST. ROWS|COST   |
---------------------------------------
|0 |HASH JOIN  |    |9801000  |5933109|
|1 | TABLE SCAN|t2  |10000    |6219   |
|2 | TABLE SCAN|t1  |100000   |68478  |
=======================================

Outputs & filters:
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil),
      equal_conds([t1.c2 = t2.c2]), other_conds(nil)
  1 - output([t2.c2], [t2.c1]), filter(nil),
      access([t2.c2], [t2.c1]), partitions(p0)
  2 - output([t1.c2], [t1.c1]), filter(nil),
      access([t1.c2], [t1.c1]), partitions(p0)

Show an execution plan in the JSON format.

obclient> EXPLAIN FORMAT=JSON SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4\G
*************************** 1. row ***************************
Query Plan: {
  "ID":2,
  "OPERATOR":"JOIN",
  "NAME":"JOIN",
  "EST.ROWS":9800999,
  "COST":5933108,
  "output": [
    "t1.c1",
    "t1.c2",
    "t2.c1",
    "t2.c2"
  ],
  "TABLE SCAN": {
    "ID":0
    "OPERATOR":"TABLE SCAN",
    "NAME":"TABLE SCAN",
    "EST.ROWS":10000,
    "COST":6218,
    "output": [
      "t2.c2",
      "t2.c1"
    ]
  },
  "TABLE SCAN": {
    "ID":1,
    "OPERATOR":"TABLE SCAN",
    "NAME":"TABLE SCAN",
    "EST.ROWS":100000,
    "COST":68477,
    "output": [
      "t1.c2",
      "t1.c1"
    ]
  }
}