EXPLAIN output format
Output structure
The syntax structure execution result is a text description of the plan selected for statement, and you can choose to comment with execution statistics.
The following is to demonstrate the output structure by taking the query analysis of the dataset in TPCH as an example:
explain SELECT * FROM customer WHERE c_nationkey = (SELECT n_nationkey FROM nation
WHERE customer.c_nationkey = nation.n_nationkey AND nation.n_nationkey > 5);
mysql> explain SELECT * FROM customer WHERE c_nationkey = (SELECT n_nationkey FROM nation
-> WHERE customer.c_nationkey = nation.n_nationkey AND nation.n_nationkey > 5);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| QUERY PLAN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Project |
| -> Filter |
| Filter Cond: (customer.c_nationkey = nation.n_nationkey) |
| -> Join |
| Join Type: SINGLE hashOnPK |
| Join Cond: (customer.c_nationkey = nation.n_nationkey) |
| -> Table Scan on tpch.customer |
| -> Table Scan on tpch.nation |
| Filter Cond: (nation.n_nationkey > 5) |
| Block Filter Cond: (nation.n_nationkey > 5) |
+------------------------------------------------------------------------------------------------------------------------------
10 rows in set (0.01 sec)
EXPLAIN outputs a tree structure named QUERY PLAN, each leaf node contains the node type and the affected object. We are now using only node type information to simplify the presentation of the above example. The QUERY PLAN tree structure can visualize the entire process of SQL query and display the operation nodes it passes through.
Project
└── Filter
└── Join
└── Table Scan
└── Table Scan
Node Type
MatrixOne supports the following node types.
| Node Name | Meaning |
|---|---|
| Values Scan | Scan for processing values |
| Table Scan | Scan data from tables |
| External Scan | Process external data scanning |
| Source Scan | Processing data scans of source tables |
| Project | Project |
| Sink | Distribute the same data to one/multiple objects |
| Sink Scan | Read data distributed by other objects |
| Recursive Scan | Loop CTE syntax, process data at the end of each loop to determine whether the next loop is started |
| CTE Scan | Loop CTE syntax, read the data at the beginning of each loop |
| Aggregate | Aggregate data |
| Filter | Filter data |
| Join | Connect data |
| Sample | SAMPLE sampling function, sample data |
| Sort | Sort Data |
| Partition | Sort the data in the range window and divide it by value |
| Union | Combination of result sets for two or more queries |
| Union All | A combination of result sets for two or more queries, including duplicate rows |
| Window | Perform range window calculation on data |
| Time Window | Calculate the time window on the data |
| Fill | NULL value in processing time window |
| Insert | Insert data |
| Delete | Delete data |
| Intersect | A combination of rows that exist for both or more queries |
| Intersect All | A combination of rows that exist for both or more queries, including duplicate rows |
| Minus | Compare the results of two queries and return rows that exist in the first query but do not exist in the second query |
| Table Function | Read data through table functions |
| PreInsert | Organize the data to be written |
| PreInsert UniqueKey | Organize the data to be written to the unique key hidden table |
| PreInsert SecondaryKey | Organize the data to be written to the hidden table of the secondary index |
| PreDelete | The data that needs to be deleted when sorting out the partition table. |
| On Duplicate Key | Update duplicate data |
| Fuzzy Filter for duplicate key | Deduplication of written/updated data |
| Lock | Lock the operation data |
Example
VALUES Scan & Project
mysql> explain select abs(-1);
+------------------------------------+
| QUERY PLAN |
+------------------------------------+
| Project |
| -> Values Scan "*VALUES*" |
+------------------------------------+
2 rows in set (0.00 sec)
Table Scan
mysql> explain select * from customer;
+------------------------------------------+
| QUERY PLAN |
+------------------------------------------+
| Project |
| -> Table Scan on tpch.customer |
+------------------------------------------+
2 rows in set (0.01 sec)
External Scan
mysql> create external table extable(n1 int)infile{"filepath"='yourpath/xx.csv'} ;
Query OK, 0 rows affected (0.03 sec)
mysql> explain select * from extable;
+----------------------------------------+
| QUERY PLAN |
+----------------------------------------+
| Project |
| -> External Scan on db1.extable |
+----------------------------------------+
2 rows in set (0.01 sec)
Sink & Lock & Delete & Insert & PreInsert & Sink Scan
mysql> create table t3(n1 int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t3 values(1);
Query OK, 1 row affected (0.01 sec)
mysql> explain update t3 set n1=2;+-----------------------------------------------+
| QUERY PLAN |
+-----------------------------------------------+
| Plan 0: |
| Sink |
| -> Lock |
| -> Project |
| -> Project |
| -> Table Scan on tpch.t3 |
| Plan 1: |
| Delete on tpch.t3 |
| -> Sink Scan |
| DataSource: Plan 0 |
| Plan 2: |
| Insert on tpch.t3 |
| -> Project |
| -> PreInsert on tpch.t3 |
| -> Project |
| -> Sink Scan |
| DataSource: Plan 0 |
+-----------------------------------------------+
17 rows in set (0.00 sec)
Recursive Scan & CTE Scan & Filter
mysql> create table t4(n1 int,n2 int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t4 values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.01 sec)
mysql> explain WITH RECURSIVE t4_1(n1_1) AS (
-> SELECT n1 FROM t4
-> UNION all
-> SELECT n1_1 FROM t4_1 WHERE n1_1=1
-> )
-> SELECT * FROM t4_1;
+---------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+---------------------------------------------------------------------------------------------------+
| Plan 0: |
| Sink |
| -> Project |
| -> Table Scan on tpch.t4 |
| Plan 1: |
| Sink |
| -> Project |
| -> Filter |
| Filter Cond: (t4_1.n1_1 = 1), mo_check_level((t4_1.__mo_recursive_level_col < 100)) |
| -> Recursive Scan |
| DataSource: Plan 2 |
| Plan 2: |
| Sink |
| -> CTE Scan |
| DataSource: Plan 0, Plan 1 |
| Plan 3: |
| Project |
| -> Sink Scan |
| DataSource: Plan 2 |
+---------------------------------------------------------------------------------------------------+
19 rows in set (0.00 sec)
Aggregate
mysql> explain SELECT count(*) FROM NATION group by N_NAME;
+-------------------------------------------+
| QUERY PLAN |
+-------------------------------------------+
| Project |
| -> Aggregate |
| Group Key: nation.n_name |
| Aggregate Functions: starcount(1) |
| -> Table Scan on tpch.nation |
+-------------------------------------------+
5 rows in set (0.01 sec)
Join
mysql> create table t5(n1 int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t5 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
mysql> create table t6(n1 int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t5 values(3),(4),(5);
Query OK, 3 rows affected (0.01 sec)
mysql> explain SELECT * FROM t5 LEFT JOIN t6 ON t5.n1 = t6.n1;
+------------------------------------+| QUERY PLAN |
+------------------------------------+
| Project |
| -> Join |
| Join Type: LEFT |
| Join Cond: (t5.n1 = t6.n1) |
| -> Table Scan on tpch.t5 |
| -> Table Scan on tpch.t6 |
+------------------------------------+
6 rows in set (0.00 sec)
Sample
mysql> explain SELECT SAMPLE(c_address, 90 percent) FROM customer;
+-----------------------------------------------------+
| QUERY PLAN |
+-----------------------------------------------------+
| Project |
| -> Sample |
| Sample 90.00 Percent by: customer.c_address |
| -> Table Scan on tpch.customer |
+-----------------------------------------------------+
4 rows in set (0.00 sec)
SORT
mysql> explain select * from customer order by c_custkey;
+-----------------------------------------------+
| QUERY PLAN |
+-----------------------------------------------+
| Project |
| -> Sort |
| Sort Key: customer.c_custkey INTERNAL |
| -> Table Scan on tpch.customer |
+-----------------------------------------------+
4 rows in set (0.00 sec)
Partition & Window
mysql>CREATE TABLE t7(n1 int,n2 int);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t7 values(1,3),(2,2),(3,1);
Query OK, 3 rows affected (0.01 sec)
mysql> explain SELECT SUM(n1) OVER(PARTITION BY n2) AS sn1 FROM t7;
+----------------------------------------------------------+
| QUERY PLAN |
+----------------------------------------------------------+
| Project |
| -> Window |
| Window Function: sum(t7.n1); Partition By: t7.n2 |
| -> Partition |
| Sort Key: t7.n2 INTERNAL |
| -> Table Scan on tpch.t7 |
+----------------------------------------------------------+
6 rows in set (0.01 sec)
Time window & Fill
mysql> CREATE TABLE sensor_data (ts timestamp(3) primary key, temperature FLOAT);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO sensor_data VALUES('2023-08-01 00:00:00', 25.0);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO sensor_data VALUES('2023-08-01 00:05:00', 26.0);
Query OK, 1 row affected (0.01 sec)
mysql> explain select _wstart, _wend from sensor_data interval(ts, 10, minute) fill(prev);
+---------------------------------------------------+
| QUERY PLAN |
+---------------------------------------------------+
| Project |
| -> Fill |
| Fill Columns: |
| Fill Mode: Prev |
| -> Time window |
| Sort Key: sensor_data.ts |
| Aggregate Functions: _wstart, _wend |
| -> Table Scan on db2.sensor_data |
+---------------------------------------------------+
8 rows in set (0.00 sec)
Intersect
mysql> explain select * from t5 intersect select * from t6;
+-----------------------------------------+
| QUERY PLAN |
+-----------------------------------------+
| Project |
| -> Intersect |
| -> Project |
| -> Table Scan on tpch.t5 |
| -> Project |
| -> Table Scan on tpch.t6 |
+-----------------------------------------+
6 rows in set (0.00 sec)
Intersect All
mysql> explain select * from t5 intersect all select * from t6;
+-----------------------------------------+
| QUERY PLAN |
+-----------------------------------------+
| Project |
| -> Intersect All |
| -> Project |
| -> Table Scan on tpch.t5 |
| -> Project |
| -> Table Scan on tpch.t6 |
+-----------------------------------------+
6 rows in set (0.00 sec)
Minus
mysql> explain select * from t5 minus select * from t6;
+-----------------------------------------+
| QUERY PLAN |+-----------------------------------------+
| Project |
| -> Minus |
| -> Project |
| -> Table Scan on tpch.t5 |
| -> Project |
| -> Table Scan on tpch.t6 |
+-----------------------------------------+
6 rows in set (0.00 sec)
Table Function
mysql> explain select * from unnest('{"a":1}') u;
+-------------------------------------+
| QUERY PLAN |
+-------------------------------------+
| Project |
| -> Table Function on unnest |
| -> Values Scan "*VALUES*" |
+-------------------------------------+
3 rows in set (0.10 sec)
PreInsert UniqueKey & Fuzzy Filter for duplicate key
mysql> CREATE TABLE t8(n1 int,n2 int UNIQUE key);
Query OK, 0 rows affected (0.01 sec)
mysql> explain INSERT INTO t8(n2) values(1);
+---------------------------------------------------------------------------------+
| QUERY PLAN |
+---------------------------------------------------------------------------------+
| Plan 0: |
| Sink |
| -> PreInsert on tpch.t8 |
| -> Project |
| -> Project |
| -> Values Scan "*VALUES*" |
| Plan 1: |
| Sink |
| -> Lock |
| -> PreInsert UniqueKey |
| -> Sink Scan |
| DataSource: Plan 0 |
| Plan 2: |
| Insert on tpch.__mo_index_unique_018e2d16-6629-719d-82b5-036222e9658a |
| -> Sink Scan |
| DataSource: Plan 1 |
| Plan 3: |
| Fuzzy Filter for duplicate key |
| -> Table Scan on tpch.__mo_index_unique_018e2d16-6629-719d-82b5-036222e9658a |
| Filter Cond: (__mo_index_idx_col = 1) |
| Block Filter Cond: (__mo_index_idx_col = 1) |
| -> Sink Scan |
| DataSource: Plan 1 |
| Plan 4: |
| Insert on tpch.t8 |
| -> Sink Scan |
| DataSource: Plan 0 |
+---------------------------------------------------------------------------------+
27 rows in set (0.01 sec)
PreInsert SecondaryKey
mysql> CREATE TABLE t9 ( n1 int , n2 int, KEY key2 (n2) USING BTREE);
Query OK, 0 rows affected (0.02 sec)
mysql> explain INSERT INTO t9(n2) values(2);
+--------------------------------------------------------------------------+
| QUERY PLAN |
+--------------------------------------------------------------------------+
| Plan 0: |
| Sink |
| -> PreInsert on tpch.t9 |
| -> Project |
| -> Project |
| -> Values Scan "*VALUES*" |
| Plan 1: |
| Insert on tpch.__mo_index_secondary_018e2d14-6f20-7db0-babb-c1fd505fd3c5 |
| -> Lock |
| -> PreInsert SecondaryKey || -> Sink Scan |
| DataSource: Plan 0 |
| Plan 2: |
| Insert on tpch.t9 |
| -> Sink Scan |
| DataSource: Plan 0 |
+--------------------------------------------------------------------------+
16 rows in set (0.00 sec)