EXPLAIN 输出格式
- 输出结构
- 节点类型
  - Table Scan
  - Values Scan
  - Project
  - Aggregate
  - Filter
  - Join
  - Sort

EXPLAIN 输出格式

输出结构

语法结构执行结果是为 statement 选择的计划的文本描述，可以选择使用执行统计信息进行注释。

以下以 SQL 为例，演示输出结构：

1. explain select city,libname1,count(libname1) as a from t3 join t1 on libname1=libname3 join t2 on isbn3=isbn2 group by city,libname1;

1. +--------------------------------------------------------------------------------------------+
2. | QUERY PLAN                                                                                 |
3. +--------------------------------------------------------------------------------------------+
4. | Project(cost=0.00..0.00 card=400.00 ndv=0.00 rowsize=0                                     |
5. |   ->  Aggregate(cost=0.00..0.00 card=400.00 ndv=0.00 rowsize=0                             |
6. |         Group Key:#[0,1], #[0,0]                                                           |
7. |         Aggregate Functions: count(#[0,0])                                                 |
8. |         ->  Join(cost=0.00..0.00 card=400.00 ndv=0.00 rowsize=0                            |
9. |               Join Type: INNER                                                             |
10. |               Join Cond: (#[1,2] = #[0,0])                                                 |
11. |               ->  Table Scan on abc.t2(cost=0.00..0.00 card=8.00 ndv=0.00 rowsize=0        |
12. |               ->  Join(cost=0.00..0.00 card=50.00 ndv=0.00 rowsize=0                       |
13. |                     Join Type: INNER                                                       |
14. |                     Join Cond: (#[0,0] = #[1,1])                                           |
15. |                     ->  Table Scan on abc.t1(cost=0.00..0.00 card=5.00 ndv=0.00 rowsize=0  |
16. |                     ->  Table Scan on abc.t3(cost=0.00..0.00 card=10.00 ndv=0.00 rowsize=0 |
17. +--------------------------------------------------------------------------------------------+
18. 13 rows in set (0.00 sec)

EXPLAIN 输出一个名称为 Execution Plan Tree 树形结构，每个叶子节点都包含节点类型、受影响的对象以及其他属性的信息，如 cost，rowsize 等。我们现在只使用节点类型信息来简化展示上面的示例。Execution Plan Tree 树形结构可以可视化 SQL 查询的整个过程，显示它所经过的操作节点以及它们的成本估计。

1. Project
2. └── Aggregate
3. └── Join
4. └── Table Scan
5. └── Join
6. └──Table Scan
7. └──Table Scan

节点类型

MatrixOne 支持以下节点类型。

节点类型	Explain 中的命名
Node_TABLE_SCAN	Table Scan
Node_VALUE_SCAN	Values Scan
Node_PROJECT	Project
Node_AGG	Aggregate
Node_FILTER	Filter
Node_JOIN	Join
Node_SORT	Sort
Node_INSERT	Insert
Node_UPDATE	Update
Node_DELETE	Delete

Table Scan

特性	格式	描述
cost	cost=0.00..0.00	The first is estimated start-up cost. This is the time expended before the output phase can begin, e.g., time to do the sorting in a sort node. The second is estimated total cost. This is stated on the assumption that the plan node is run to completion, i.e., all available rows are retrieved. In practice a node’s parent node might stop short of reading all available rows (see the `LIMIT` example below).
card	card=14.00	Estimated column cardinality.
ndv	ndv=0.00	Estimated number of distinct values.
rowsize	rowsize=0.00	Estimated rowsize.
output	Output: #[0,0], #[0,1], #[0,2], #[0,3], #[0,4], #[0,5], #[0,6], #[0,7]	Node output information.
Table	Table : ‘emp’ (0:’empno’, 1:’ename’, 2:’job’, 3:’mgr’,)	Table definition information after column pruning.
Filter Cond	Filter Cond: (CAST(#[0,5] AS DECIMAL128) > CAST(20 AS DECIMAL128))	Filter condition.

Values Scan

特性	格式	描述
cost	(cost=0.00..0.00 card=14.00 ndv=0.00 rowsize=0)	Estimated cost
output	Output: 0	Node output information

Project

特性	格式	描述
cost	(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)	Estimated cost
output	Output: (CAST(#[0,0] AS INT64) + 2)	Node output information

Aggregate

特性	格式	描述
cost	(cost=0.00..0.00 card=14.00 ndv=0.00 rowsize=0)	Estimated cost
output	Output: #[0,0], #[0,1], #[0,2], #[0,3], #[0,4], #[0,5], #[0,6], #[0,7]	Node output information
Group Key	Group Key:#[0,0]	Key for grouping
Aggregate Functions	Aggregate Functions: max(#[0,1])	Aggregate function name

Filter

特性	格式	描述
cost	(cost=0.00..0.00 card=14.00 ndv=0.00 rowsize=0)	Estimated cost
output	Output: #[0,0], #[0,1], #[0,2], #[0,3], #[0,4], #[0,5], #[0,6], #[0,7]	Node output information
Filter Cond	Filter Cond: (CAST(#[0,1] AS INT64) > 10)	Filter condition

Join

特性	格式	描述
cost	(cost=0.00..0.00 card=14.00 ndv=0.00 rowsize=0)	Estimated cost
output	Output: #[0,0]	Node output information
Join Type: INNER	Join Type: INNER	Join type
Join Cond	Join Cond: (#[0,0] = #[1,0])	Join condition

Sort

特性	格式	描述
cost	(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)	Estimated cost
output	Output: #[0,0], #[0,1], #[0,2], #[0,3], #[0,4], #[0,5], #[0,6], #[0,7]	Node output information
Sort Key	Sort Key: #[0,0] DESC, #[0,1] INTERNAL	Sort key
Limit	Limit: 10	Number limit for output data
Offset	Offset: 20	Number offset for output data

EXPLAIN Output Format