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.

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:

Table Scan

Values Scan

Project

Aggregate

Filter

Join

Sort