Output Structure
The command's result is a textual description of the plan selected for the statement
, optionally annotated with execution statistics.
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