EXPLAIN Output Format

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.

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 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.

1. Project
2. └── Aggregate
3. └── Join
4. └── Table Scan
5. └── Join
6. └──Table Scan
7. └──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