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_SCANTable Scan
Node_VALUE_SCANValues Scan
Node_PROJECTProject
Node_AGGAggregate
Node_FILTERFilter
Node_JOINJoin
Node_SORTSort
Node_INSERTInsert
Node_UPDATEUpdate
Node_DELETEDelete

Table Scan

特性格式描述
costcost=0.00..0.00The 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).
cardcard=14.00Estimated column cardinality.
ndvndv=0.00Estimated number of distinct values.
rowsizerowsize=0.00Estimated rowsize.
outputOutput: #[0,0], #[0,1], #[0,2], #[0,3], #[0,4], #[0,5], #[0,6], #[0,7]Node output information.
TableTable : ‘emp’ (0:’empno’, 1:’ename’, 2:’job’, 3:’mgr’,)Table definition information after column pruning.
Filter CondFilter 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
outputOutput: 0Node output information

Project

特性格式描述
cost(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)Estimated cost
outputOutput: (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
outputOutput: #[0,0], #[0,1], #[0,2], #[0,3], #[0,4], #[0,5], #[0,6], #[0,7]Node output information
Group KeyGroup Key:#[0,0]Key for grouping
Aggregate FunctionsAggregate Functions: max(#[0,1])Aggregate function name

Filter

特性格式描述
cost(cost=0.00..0.00 card=14.00 ndv=0.00 rowsize=0)Estimated cost
outputOutput: #[0,0], #[0,1], #[0,2], #[0,3], #[0,4], #[0,5], #[0,6], #[0,7]Node output information
Filter CondFilter 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
outputOutput: #[0,0]Node output information
Join Type: INNERJoin Type: INNERJoin type
Join CondJoin Cond: (#[0,0] = #[1,0])Join condition

Sort

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