ByteHouse Support project Pass the EXPLAIN statement is SQL statement generate the corresponding Run Plan, Help you Understood how ByteHouse Run Search, making it easier for you to optimize SQL statements in a targeted manner. This article uses the SQL in TPCH-Q1 as an example to demonstrate how to use different Type of EXPLAIN statements.

In ByteHouse, the optimizer (Query Optimizer) achieves end-to-end query optimization through Rules-based optimization (Rule-Based Optimization, RBO), cost-based optimization (Cost-Based Optimization, CBO), Native distributed Plan collaboration, and advanced capabilities (dynamic filter pushdown/materialized view rewriting). After Activate the optimizer, executing the EXPLAIN statement will generate the corresponding Run Plan for the SQL statement, Show the Details of the Run Plan, and generate a more efficient Run Plan. This can Help developers quickly identify SQL performance bottlenecks and optimize index design, Expression complexity, or distributed execution Strategy in a targeted manner, ensuring results are returned quickly with Min Resources consumption.
ByteHouse currently supports the following Type of EXPLAIN statements:

| | | \

EXPLAIN [DISTRIBUTED | ANALYZE | AST | SYNTAX | METADATA ] [setting = value, ...] 
    [ 
      SELECT ... | 
      tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...] 
    ] 
    [FORMAT ...]

parameter description #

| | | | \

Example #

EXPLAIN json=0 
SELECT 
    l_returnflag, 
    l_linestatus, 
    sum(l_quantity) AS sum_qty, 
    sum(l_extendedprice) AS sum_base_price, 
    sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price, 
    sum((l_extendedprice * (1 - l_discount)) * (1 + l_tax)) AS sum_charge, 
    avg(l_quantity) AS avg_qty, 
    avg(l_extendedprice) AS avg_price, 
    avg(l_discount) AS avg_disc, 
    count(*) AS count_order 
FROM lineitem 
WHERE l_shipdate <= (toDate('1998-12-01') - toIntervalDay('90')) 
GROUP BY 
    l_returnflag, 
    l_linestatus 
ORDER BY 
    l_returnflag ASC, 
    l_linestatus ASC

Output result

EXPLAIN DISTRIBUTED #

This statement is used to show the distributed execution plan.

Syntax

EXPLAIN DISTRIBUTED <SQL>;

Example

EXPLAIN DISTRIBUTED 
SELECT 
    l_returnflag, 
    l_linestatus, 
    sum(l_quantity) AS sum_qty, 
    sum(l_extendedprice) AS sum_base_price, 
    sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price, 
    sum((l_extendedprice * (1 - l_discount)) * (1 + l_tax)) AS sum_charge, 
    avg(l_quantity) AS avg_qty, 
    avg(l_extendedprice) AS avg_price, 
    avg(l_discount) AS avg_disc, 
    count(*) AS count_order 
FROM lineitem 
WHERE l_shipdate <= (toDate('1998-12-01') - toIntervalDay('90')) 
GROUP BY 
    l_returnflag, 
    l_linestatus 
ORDER BY 
    l_returnflag ASC, 
    l_linestatus ASC

Output result

EXPLAIN ANALYZE #

EXPLAIN ANALYZE is used to show the execution plan with runtime information, and also supports showing the distributed execution plan with runtime information through EXPLAIN ANALYZE DISTRIBUTED.
The general setting parameters supported by this type of statement are as follows:

| | | | \

EXPLAIN ANALYZE

This statement is used to show the execution plan with runtime information. For specific field descriptions, see Explain Analyze field description.

Syntax

EXPLAIN ANALYZE <SQL>;

Parameter description

In addition to general parameters, EXPLAIN ANALYZE also supports the following setting parameters:

• processor_profile_events: Set whether to output ProfileEvents metrics on each PlanNode during plan execution. When set to 1, the system will output relevant content and aggregate by node. When set to 0, no output will be generated. Example: processor_profile_events = 1.

Example

EXPLAIN ANALYZE 
SELECT 
    l_returnflag, 
    l_linestatus, 
    sum(l_quantity) AS sum_qty, 
    sum(l_extendedprice) AS sum_base_price, 
    sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price, 
    sum((l_extendedprice * (1 - l_discount)) * (1 + l_tax)) AS sum_charge, 
    avg(l_quantity) AS avg_qty, 
    avg(l_extendedprice) AS avg_price, 
    avg(l_discount) AS avg_disc, 
    count(*) AS count_order 
FROM lineitem 
WHERE l_shipdate <= (toDate('1998-12-01') - toIntervalDay('90')) 
GROUP BY 
    l_returnflag, 
    l_linestatus 
ORDER BY 
    l_returnflag ASC, 
    l_linestatus ASC

Output

EXPLAIN ANALYZE DISTRIBUTED

This statement is used to display the distributed execution plan with runtime information. For detailed field descriptions, see Explain Analyze field description.

Syntax

EXPLAIN ANALYZE DISTRIBUTED <SQL>;

Parameter description

In addition to general parameters, EXPLAIN ANALYZE DISTRIBUTED also supports the following setting parameters:

• processor_profile_events: Set whether to output ProfileEvents metrics for each PlanNode during plan execution. When set to 1, the system will output relevant content and aggregate by node. When set to 0, no output will be generated. Example: processor_profile_events = 1.
• profile_events_list: Can be used to output ProfileEvents during distributed plan execution. By default, aggregation is enabled. To display ProfileEvents for each node separately, set aggregate_profiles=0. Example: profile_events_list='QueryAnalyzerTime'. If there are multiple parameter values, use a comma (,) to separate them.
• aggregate_profiles: Set aggregate_profiles = 1 to print the aggregated profile for each worker. Set aggregate_profiles = 0 and the system will print profiles for each worker separately.

Example

EXPLAIN ANALYZE DISTRIBUTED 
SELECT 
    l_returnflag, 
    l_linestatus, 
    sum(l_quantity) AS sum_qty, 
    sum(l_extendedprice) AS sum_base_price, 
    sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price, 
    sum((l_extendedprice * (1 - l_discount)) * (1 + l_tax)) AS sum_charge, 
    avg(l_quantity) AS avg_qty, 
    avg(l_extendedprice) AS avg_price, 
    avg(l_discount) AS avg_disc, 
    count(*) AS count_order 
FROM lineitem 
WHERE l_shipdate <= (toDate('1998-12-01') - toIntervalDay('90')) 
GROUP BY 
    l_returnflag, 
    l_linestatus 
ORDER BY 
    l_returnflag ASC, 
    l_linestatus ASC

Output

EXPLAIN AST #

Dumps the abstract syntax tree (AST) of the query, supporting all types of queries, not limited to SELECT.

Syntax

EXPLAIN AST <SQL>;

Example

EXPLAIN AST 
SELECT 
    l_returnflag, 
    l_linestatus, 
    sum(l_quantity) AS sum_qty, 
    sum(l_extendedprice) AS sum_base_price, 
    sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price, 
    sum((l_extendedprice * (1 - l_discount)) * (1 + l_tax)) AS sum_charge, 
    avg(l_quantity) AS avg_qty, 
    avg(l_extendedprice) AS avg_price, 
    avg(l_discount) AS avg_disc, 
    count(*) AS count_order 
FROM lineitem 
WHERE l_shipdate <= (toDate('1998-12-01') - toIntervalDay('90')) 
GROUP BY 
    l_returnflag, 
    l_linestatus 
ORDER BY 
    l_returnflag ASC, 
    l_linestatus ASC

Output

EXPLAIN SYNTAX #

Returns the query after syntax optimization.

Syntax

EXPLAIN SYNTAX <SQL>;

Example

EXPLAIN SYNTAX 
SELECT 
    l_returnflag, 
    l_linestatus, 
    sum(l_quantity) AS sum_qty, 
    sum(l_extendedprice) AS sum_base_price, 
    sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price, 
    sum((l_extendedprice * (1 - l_discount)) * (1 + l_tax)) AS sum_charge, 
    avg(l_quantity) AS avg_qty, 
    avg(l_extendedprice) AS avg_price, 
    avg(l_discount) AS avg_disc, 
    count(*) AS count_order 
FROM lineitem 
WHERE l_shipdate <= (toDate('1998-12-01') - toIntervalDay('90')) 
GROUP BY 
    l_returnflag, 
    l_linestatus 
ORDER BY 
    l_returnflag ASC, 
    l_linestatus ASC

Output

EXPLAIN METADATA #

This statement is used to display information parsed during the SQL Analyzer phase. The parsed information mainly includes database name, table name, column name, function name, settings, and others.

Limitations

• Only used in the form EXPLAIN METADATA <SQL>;
• Only effective for SELECT and INSERT SELECT queries.

Syntax

EXPLAIN METADATA <SQL>;

Parameter description

EXPLAIN METADATA supports the following setting parameters:

• format_json: Set format_json = 1 to output content in JSON format.
• lineage: Set whether to output SQL lineage information. Set to 1 to display, set to 0 to not display. Example: lineage = 1.

Example 1

explain metadata select t1.a, t2.b from metadata t1 join metadata2 t2 on t1.a=t2.a settings enable_optimizer=1;

Output

| | | | | | | \

The field descriptions are as follows:

• tables: Tables involved in the query, array type.
• databases: The database corresponding to the tables field, array(String) type.
• column_lists: Columns used in the current query for each table in the tables field, array(array(String)) type.
• used_function_names: Function names used in the query, array(String) type.
• settings: Settings names effective in the query, map(string, string) type.
• insert_info: Database name, table name, and column name for insert, array(array(String)) type.

Example 2

EXPLAIN METADATA format_json = 1, lineage = 1 
WITH t3 AS 
    ( 
        SELECT 
            count() AS a, 
            sum(b) AS b 
        FROM cache2 
        GROUP BY c 
    ) 
SELECT 
    a, 
    b 
FROM 
( 
    ( 
        SELECT 
            t3.b AS a, 
            t2.b AS b 
        FROM cache AS t2 
        INNER JOIN t3 ON t2.a = t3.a 
    ) 
    UNION ALL 
    ( 
        SELECT 
            * 
        FROM t3 
    ) 
) AS t1 
WHERE t1.a = 1 
SETTINGS enable_optimizer = 1 
FORMAT TabSeparatedRaw

Output

{ 
 "UsedTablesInfo" : [ 
  { 
   "Database" : "test", 
   "Table" : "cache", 
   "Columns" : [ 
    "a", 
    "b" 
   ] 
  }, 
  { 
   "Database" : "test", 
   "Table" : "cache2", 
   "Columns" : [ 
    "b", 
    "c" 
   ] 
  } 
 ], 
 "UsedFunctions" : [ 
  "count", 
  "equals", 
  "sum" 
 ], 
 "UsedSettings" : { 
  "enable_optimizer" : "1" 
 }, 
 "InsertInfo" : { 
 }, 
 "FunctionsInfo" : [ 

 ], 
 "LineageInfo" : { 
  "TableSources" : [ 
   { 
    "Database" : "test", 
    "Table" : "cache2", 
    "Columns" : [ 
     { 
      "Id" : 3, 
      "Name" : "b" 
     }, 
     { 
      "Id" : 2, 
      "Name" : "c" 
     } 
    ] 
   }, 
   { 
    "Database" : "test", 
    "Table" : "cache", 
    "Columns" : [ 
     { 
      "Id" : 1, 
      "Name" : "a" 
     }, 
     { 
      "Id" : 0, 
      "Name" : "b" 
     } 
    ] 
   } 
  ], 
  "ExpressionSources" : [ 
   { 
    "Sources" : [ 
     { 
      "Database" : "test", 
      "Table" : "cache2" 
     } 
    ], 
    "Expression" : [ 
     { 
      "Id" : 4, 
      "Name" : "expr#count()" 
     } 
    ] 
   } 
  ], 
  "OutputLineageInfo" : [ 
   { 
    "Name" : "b", 
    "SourceIds" : [ 
     3, 
     0 
    ] 
   }, 
   { 
    "Name" : "a", 
    "SourceIds" : [ 
     4, 
     3 
    ] 
   } 
  ], 
  "InsertLinageInfo" : { 
  } 
 } 
}

The field descriptions are as follows:

• TableSources: Original column information for all tables in the query, where each column corresponds to an ID.
• ExpressionSources: Information in the query generated by expressions, constants, or aggregate functions for which original column information cannot be found. Each expression also corresponds to an ID. The Sources field contains the table information involved in generating the expression.
• OutputLineageInfo: All output field names in the query and the corresponding IDs of original columns or expressions. Name is the output field name, and SourceIds are the IDs of the corresponding columns or expressions.
• InsertLineageInfo: The mapping between the tables and columns inserted in an insert select statement and the output field names of the corresponding select clause. InsertColumnName is the column name in the insert statement, and InputName is the output field name in OutputLineageInfo.