# EXPLAIN output format (example-tables)= ## Example tables These minimal tables let you inspect the example plan in a test database you own. Plan nodes and statistics depend on data, indexes, and the engine version. ```sql CREATE TABLE nation (n_nationkey INT PRIMARY KEY); CREATE TABLE customer (c_custkey INT PRIMARY KEY, c_nationkey INT); INSERT INTO nation VALUES (6), (7); INSERT INTO customer VALUES (1, 6), (2, 7); ``` ## 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 query uses the [example tables](#example-tables). The output below illustrates the plan structure; actual nodes and statistics depend on the instance. ```sql explain SELECT * FROM customer WHERE c_nationkey = (SELECT n_nationkey FROM nation WHERE customer.c_nationkey = nation.n_nationkey AND nation.n_nationkey > 5); ``` ```text 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 customer | | -> Table Scan on 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. ```text 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 | Projecting data | | 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 ```text mysql> explain select abs(-1); +------------------------------------+ | QUERY PLAN | +------------------------------------+ | Project | | -> Values Scan "*VALUES*" | +------------------------------------+ 2 rows in set (0.00 sec) ``` ### Table Scan ```text mysql> explain select * from customer; +------------------------------------------+ | QUERY PLAN | +------------------------------------------+ | Project | | -> Table Scan on customer | +------------------------------------------+ 2 rows in set (0.01 sec) ``` ### External Scan ```text 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 ```text 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 ```text 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 ```text 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 nation | +-------------------------------------------+ 5 rows in set (0.01 sec) ``` ### Join ```text 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 ```text 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 customer | +-----------------------------------------------------+ 4 rows in set (0.00 sec) ``` ### SORT ```text mysql> explain select * from customer order by c_custkey; +-----------------------------------------------+ | QUERY PLAN | +-----------------------------------------------+ | Project | | -> Sort | | Sort Key: customer.c_custkey INTERNAL | | -> Table Scan on customer | +-----------------------------------------------+ 4 rows in set (0.00 sec) ``` ### Partition & Window ```text 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 ```text 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 ```text 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 ```text 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 ```text 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 ```text 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 ```text 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 ```text 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) ```