Output Structure
The command's result is a textual description of the plan selected for the statement
Take the following SQL as an example, we demonstrate the output structure.
explain select city,libname1,count(libname1) as a from t3 join t1 on libname1=libname3 join t2 on isbn3=isbn2 group by city,libname1;
+--------------------------------------------------------------------------------------------+
| QUERY PLAN |
+--------------------------------------------------------------------------------------------+
| Project(cost=0.00..0.00 card=400.00 ndv=0.00 rowsize=0 |
| -> Aggregate(cost=0.00..0.00 card=400.00 ndv=0.00 rowsize=0 |
| Group Key:#[0,1], #[0,0] |
| Aggregate Functions: count(#[0,0]) |
| -> Join(cost=0.00..0.00 card=400.00 ndv=0.00 rowsize=0 |
| Join Type: INNER |
| Join Cond: (#[1,2] = #[0,0]) |
| -> Table Scan on abc.t2(cost=0.00..0.00 card=8.00 ndv=0.00 rowsize=0 |
| -> Join(cost=0.00..0.00 card=50.00 ndv=0.00 rowsize=0 |
| Join Type: INNER |
| Join Cond: (#[0,0] = #[1,1]) |
| -> Table Scan on abc.t1(cost=0.00..0.00 card=5.00 ndv=0.00 rowsize=0 |
| -> Table Scan on abc.t3(cost=0.00..0.00 card=10.00 ndv=0.00 rowsize=0 |
+--------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)
EXPLAIN outputs a tree structure, named as Execution Plan Tree. Every leaf node includes the information of node type, affected objects and other properties such as cost, rowsize and so on. We can simplify the above example only with node type information. It visualizes the whole process of a SQL query, shows which operation nodes it goes through and what are their cost estimation.
Project
└── Aggregate
└── Join
└── Table Scan
└── Join
└──Table Scan
└──Table Scan
Node types
MatrixOne supports the following node types:
Node Type
Name in 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
Property
Format
Description
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
Property
Format
Description
cost
(cost=0.00..0.00 card=14.00 ndv=0.00 rowsize=0)
Estimated cost
output
Output: 0
Node output information
Project
Property
Format
Description
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
Property
Format
Description
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
Property
Format
Description
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
Property
Format
Description
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
Property
Format
Description
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