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

In 0.5.0 version, the following node types are supported.

Node TypeName in Explain
Node_VALUE_SCANValues Scan
Node_TABLE_SCANTable Scan
Node_PROJECTProject
Node_AGGAggregate
Node_FILTERFilter
Node_JOINJoin
Node_SORTSort
Node_INSERTInsert
Node_UPDATEUpdate
Node_DELETEDelete

Table Scan

PropertyFormatDescription
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

PropertyFormatDescription
cost(cost=0.00..0.00 card=14.00 ndv=0.00 rowsize=0)Estimated cost
outputOutput: 0Node output information

Project

PropertyFormatDescription
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

PropertyFormatDescription
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

PropertyFormatDescription
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

PropertyFormatDescription
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

PropertyFormatDescription
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