Explain is a query and analysis tool that is used to evaluate how query statements are executed. Explain Analyze is a query performance analysis tool that can provide information of actual execution statements.

Syntax

V3.1.3 and later can display plans that are in the clearer formats and costs after execution. These plans include the following syntax:
  • explain (format text): view information of a formatted plan.
  • explain analyze: view the distributed plan and actual execution costs.
Explain
EXPLAIN (format text)
      SELECT count(*)
      FROM    
      grade g, student s, course c
      WHERE 
      g.id = s.id 
      AND g.course = c.cid
      AND c.cname = 'Chinese';

The following output of the sample EXPLAIN statement is returned:

| Output[count(*)]
│   Outputs: [count:bigint]
│   Estimates: {rows: 1 (8B)}
│   count(*) := count
└─ Aggregate(FINAL)
   │   Outputs: [count:bigint]
   │   Estimates: {rows: 1 (8B)}
   │   count := count(`count_2`)
   └─ LocalExchange[SINGLE] ()
      │   Outputs: [count_0_2:bigint]
      │   Estimates: {rows: 1 (8B)}
      └─ RemoteExchange[GATHER]
         │   Outputs: [count_0_2:bigint]
         │   Estimates: {rows: 1 (8B)}
         └─ Aggregate(PARTIAL)
            │   Outputs: [count_0_2:bigint]
            │   Estimates: {rows: 1 (8B)}
            │   count_2 := count(*)
            └─ InnerJoin[(`expr` = `cid`)][$hashvalue_0_5, $hashvalue_0_6]
               │   Outputs: []
               │   Estimates: {rows: 128 (1kB)}
               │   Distribution: REPLICATED
               ├─ Project[]
               │  │   Outputs: [expr:bigint, $hashvalue_0_5:bigint]
               │  │   Estimates: {rows: 1000 (3.91kB)}
               │  │   expr := CAST(`course` AS bigint)
               │  │   $hashvalue_5 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(CAST(`course` AS bigint)), 0))
               │  └─ InnerJoin[(`id` = `id_0_0`)][$hashvalue, $hashvalue_0_3]
               │     │   Outputs: [course:integer]
               │     │   Estimates: {rows: 1000 (3.91kB)}
               │     │   Distribution: REPLICATED
               │     ├─ ScanProject[table = adb:AdbTableHandle{schema=test4dmp, tableName=grade, partitionColumnHandles=[sid]}]
               │     │      Outputs: [id:bigint, course:integer, $hashvalue:bigint]
               │     │      Estimates: {rows: 1000 (11.72kB)}/{rows: 1000 (11.72kB)}
               │     │      $hashvalue := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`id`), 0))
               │     │      course := AdbColumnHandle{columnName=course, type=4, isIndexed=true}
               │     │      id := AdbColumnHandle{columnName=id, type=5, isIndexed=true}
               │     └─ LocalExchange[HASH][$hashvalue_0_3] ("id_0_0")
               │        │   Outputs: [id_0_0:bigint, $hashvalue_0_3:bigint]
               │        │   Estimates: {rows: 1000 (7.81kB)}
               │        └─ Project[]
               │           │   Outputs: [id_0_0:bigint, $hashvalue_0_4:bigint]
               │           │   Estimates: {rows: 1000 (7.81kB)}
               │           │   $hashvalue_4 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`id_0`), 0))
               │           └─ RemoteExchange[REPLICATE]
               │              │   Outputs: [id_0_0:bigint]
               │              │   Estimates: {rows: 1000 (7.81kB)}
               │              └─ TableScan[adb:AdbTableHandle{schema=test4dmp, tableName=student, partitionColumnHandles=[id]}]
               │                     Outputs: [id_0_0:bigint]
               │                     Estimates: {rows: 1000 (7.81kB)}
               │                     id_0 := AdbColumnHandle{columnName=id, type=5, isIndexed=true}
               └─ LocalExchange[HASH][$hashvalue_0_6] ("cid")
                  │   Outputs: [cid:bigint, $hashvalue_0_6:bigint]
                  │   Estimates: {rows: 128 (1kB)}
                  └─ Project[]
                     │   Outputs: [cid:bigint, $hashvalue_0_7:bigint]
                     │   Estimates: {rows: 128 (1kB)}
                     │   $hashvalue_7 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`cid`), 0))
                     └─ RemoteExchange[REPLICATE]
                        │   Outputs: [cid:bigint]
                        │   Estimates: {rows: 128 (1kB)}
                        └─ TableScan[adb:AdbTableHandle{schema=test4dmp, tableName=course, partitionColumnHandles=[cid]}]
                               Outputs: [cid:bigint]
                               Estimates: {rows: 128 (1kB)}
                               cid := AdbColumnHandle{columnName=cid, type=5, isIndexed=true}
Analysis of the execution plan
EXPLAIN ANALYZE
      SELECT count(*)
      FROM    
      grade g, student s, course c
      WHERE 
      g.id = s.id 
      AND g.course = c.cid
      AND c.cname = 'Chinese';

The following output of the sample EXPLAIN ANALYZE statement is returned:

| Fragment 1 [SINGLE]
    Output: 1 row (9B), PeakMemory: 40KB, WallTime: 4.00ns, Input: 4 rows (36B); per task: avg.: 4.00 std.dev.: 0.00
    Output layout: [count]
    Output partitioning: SINGLE []
    Aggregate(FINAL)
    │   Outputs: [count:bigint]
    │   Estimates: {rows: 1 (8B)}
    │   Output: 2 rows (18B), PeakMemory: 24B (0.00%), WallTime: 257.01us (0.14%)
    │   count := count(`count_2`)
    └─ LocalExchange[SINGLE] ()
       │   Outputs: [count_0_2:bigint]
       │   Estimates: {rows: 1 (8B)}
       │   Output: 8 rows (72B), PeakMemory: 8KB (0.39%), WallTime: 167.86us (0.09%)
       └─ RemoteSource[2]
              Outputs: [count_0_2:bigint]
              Estimates:
              Output: 4 rows (36B), PeakMemory: 16KB (0.78%), WallTime: 111.02us (0.06%)
              Input avg.: 0.50 rows, Input std.dev.: 264.58%

Fragment 2 [adb:AdbPartitioningHandle{schema=test4dmp, tableName=grade, dimTable=false, shards=4, tableEngineType=Cstore, partitionColumns=sid, prunedBuckets= empty}]
    Output: 4 rows (36B), PeakMemory: 1MB, WallTime: 133.00ns, Input: 2104 rows (32.16kB); per task: avg.: 2104.00 std.dev.: 0.00
    Output layout: [count_0_2]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count_0_2:bigint]
    │   Estimates: {rows: 1 (8B)}
    │   Output: 8 rows (72B), PeakMemory: 72B (0.00%), WallTime: 410.48us (0.23%)
    │   count_2 := count(*)
    └─ INNER Join[(`expr` = `cid`)][$hashvalue_0_5, $hashvalue_0_6]
       │   Outputs: []
       │   Estimates: {rows: 128 (0B)}
       │   Output: 199 rows (1.83kB), PeakMemory: 390KB (18.75%), WallTime: 10.73ms (6.02%)
       │   Left (probe) Input avg.: 250.00 rows, Input std.dev.: 173.21%
       │   Right (build) Input avg.: 13.00 rows, Input std.dev.: 264.58%
       │   Distribution: REPLICATED
       ├─ Project[]
       │  │   Outputs: [expr:bigint, $hashvalue_0_5:bigint]
       │  │   Estimates: {rows: 1000 (3.91kB)}
       │  │   Output: 1000 rows (17.58kB), PeakMemory: 24KB (1.17%), WallTime: 64.22us (0.04%)
       │  │   Input avg.: 250.00 rows, Input std.dev.: 173.21%
       │  │   expr := CAST(`course` AS bigint)
       │  │   $hashvalue_5 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(CAST(`course` AS bigint)), 0))
       │  └─ INNER Join[(`id` = `id_0_0`)][$hashvalue, $hashvalue_0_3]
       │     │   Outputs: [course:integer]
       │     │   Estimates: {rows: 1000 (3.91kB)}
       │     │   Output: 2000 rows (22.46kB), PeakMemory: 519KB (24.97%), WallTime: 53.56ms (30.03%)
       │     │   Left (probe) Input avg.: 250.00 rows, Input std.dev.: 173.21%
       │     │   Right (build) Input avg.: 125.00 rows, Input std.dev.: 264.58%
       │     │   Distribution: REPLICATED
       │     ├─ ScanProject[table = adb:AdbTableHandle{schema=test4dmp, tableName=grade, partitionColumnHandles=[sid]}]
       │     │      Outputs: [id:bigint, course:integer, $hashvalue:bigint]
       │     │      Estimates: {rows: 1000 (11.72kB)}/{rows: 1000 (11.72kB)}
       │     │      Output: 1000 rows (22.46kB), PeakMemory: 64KB (3.11%), WallTime: 21.97ms (12.32%)
       │     │      $hashvalue := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`id`), 0))
       │     │      course := AdbColumnHandle{columnName=course, type=4, isIndexed=true}
       │     │      id := AdbColumnHandle{columnName=id, type=5, isIndexed=true}
       │     │      Input: 1000 rows (13.67kB), Filtered: 0.00%
       │     └─ LocalExchange[HASH][$hashvalue_0_3] ("id_0_0")
       │        │   Outputs: [id_0_0:bigint, $hashvalue_0_3:bigint]
       │        │   Estimates: {rows: 1000 (7.81kB)}
       │        │   Output: 2000 rows (35.16kB), PeakMemory: 521KB (25.05%), WallTime: 14.53ms (8.15%)
       │        └─ Project[]
       │           │   Outputs: [id_0_0:bigint, $hashvalue_0_4:bigint]
       │           │   Estimates: {rows: 1000 (7.81kB)}
       │           │   Output: 1000 rows (17.58kB), PeakMemory: 24KB (1.17%), WallTime: 292.09us (0.16%)
       │           │   Input avg.: 125.00 rows, Input std.dev.: 264.58%
       │           │   $hashvalue_4 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`id_0`), 0))
       │           └─ RemoteSource[3]
       │                  Outputs: [id_0_0:bigint]
       │                  Estimates:
       │                  Output: 1000 rows (8.79kB), PeakMemory: 24KB (1.17%), WallTime: 82.91us (0.05%)
       │                  Input avg.: 125.00 rows, Input std.dev.: 264.58%
       └─ LocalExchange[HASH][$hashvalue_0_6] ("cid")
          │   Outputs: [cid:bigint, $hashvalue_0_6:bigint]
          │   Estimates: {rows: 128 (1kB)}
          │   Output: 208 rows (3.66kB), PeakMemory: 390KB (18.77%), WallTime: 10.04ms (5.63%)
          └─ Project[]
             │   Outputs: [cid:bigint, $hashvalue_0_7:bigint]
             │   Estimates: {rows: 128 (1kB)}
             │   Output: 104 rows (1.83kB), PeakMemory: 16KB (0.78%), WallTime: 295.60us (0.17%)
             │   Input avg.: 13.00 rows, Input std.dev.: 264.58%
             │   $hashvalue_7 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`cid`), 0))
             └─ RemoteSource[4]
                    Outputs: [cid:bigint]
                    Estimates:
                    Output: 104 rows (936B), PeakMemory: 16KB (0.78%), WallTime: 3.81ms (2.14%)
                    Input avg.: 13.00 rows, Input std.dev.: 264.58%

Fragment 4 [adb:AdbPartitioningHandle{schema=test4dmp, tableName=course, dimTable=false, shards=4, tableEngineType=Cstore, partitionColumns=cid, prunedBuckets= empty}]
    Output: 104 rows (936B), PeakMemory: 0B, WallTime: 20.00ns, Input: 104 rows (936B); per task: avg.: 104.00 std.dev.: 0.00
    Output layout: [cid]
    Output partitioning: BROADCAST []
    TableScan[adb:AdbTableHandle{schema=test4dmp, tableName=course, partitionColumnHandles=[cid]}]
        Outputs: [cid:bigint]
        Estimates: {rows: 128 (1kB)}
        Output: 208 rows (1.83kB), PeakMemory: 0B (0.00%), WallTime: 19.50ms (10.93%)
        cid := AdbColumnHandle{columnName=cid, type=5, isIndexed=true}

Fragment 3 [adb:AdbPartitioningHandle{schema=test4dmp, tableName=student, dimTable=false, shards=4, tableEngineType=Cstore, partitionColumns=id, prunedBuckets= empty}]
    Output: 1000 rows (8.79kB), PeakMemory: 0B, WallTime: 20.00ns, Input: 1000 rows (8.79kB); per task: avg.: 1000.00 std.dev.: 0.00
    Output layout: [id_0_0]
    Output partitioning: BROADCAST []
    TableScan[adb:AdbTableHandle{schema=test4dmp, tableName=student, partitionColumnHandles=[id]}]
        Outputs: [id_0_0:bigint]
        Estimates: {rows: 1000 (7.81kB)}
        Output: 2000 rows (17.58kB), PeakMemory: 0B (0.00%), WallTime: 20.55ms (11.52%)
        id_0 := AdbColumnHandle{columnName=id, type=5, isIndexed=true}
Parameters
  • Outputs: [symbol:type]: the output column and type of each operator.
  • Estimates: {rows: %s (%sB)}: the estimated number of rows and data volume of each operator. The estimated data determines Join Order and Data Shuffle of the optimizer.
  • PeakMemory: %s: the sum of the memory usage. This parameter is used to analyze the bottlenecks of the memory usage.
  • WallTime: %s: the sum of time when the operator is executed. Due to parallel computing, this time is not the actual execution time. This time is used to analyze computing bottlenecks.
  • Input: %s rows (%sB): the input number of rows and data volume.
  • per task: avg.: %s std.dev.: %s: the average number of rows and the standard deviation. This is used to analyze the data skew in a stage.
  • Output: %s row (%sB): the output number of rows and data volume.
  • PeakMemory: %s, WallTime: %s, Output: %s: displays the actual computed statistics at the operator level. The percentage indicates the proportion of the entire query to help you find the operator bottlenecks.

Scenarios

You can use explain to analyze some common problems of execution plans:
  1. Filter is not pushed down.

    Compare the following two SQL statements:

    SQL1: explain analyze select count(*) from test where string_test = 'a';

    SQL2: explain analyze select count(*) from test where length(string_test) = 1;

    SQL2 cannot be pushed down to store data due to functions. You must scan all data for computing. Compared with Fragment2, which is the stage of TableScan, you can draw the following conclusion:
    • The operator of SQL1 is TableScan, and the Input rows is "0 rows", which indicates that the filter is pushed down, and the number of scanned rows is 0.
    • The operator of SQL2 is ScanFilterProject, and the Input rows is "999 rows". filterPredicate = (`test4dmp`.`length`(`string_test`) = BIGINT '1')]“ indicates that no filter condition to be pushed down exists. The filter condition is not pushed down to store data, and the amount of data scanned is 999 rows.
    Fragment 2 [adb:AdbPartitioningHandle{schema=test4dmp, tableName=test, dimTable=false, shards=4, tableEngineType=Cstore, partitionColumns=id, prunedBuckets= empty}]
        Output: 4 rows (36B), PeakMemory: 0B, WallTime: 6.00ns, Input: 0 rows (0B); per task: avg.: 0.00 std.dev.: 0.00
        Output layout: [count_0_1]
        Output partitioning: SINGLE []
        Aggregate(PARTIAL)
        │   Outputs: [count_0_1:bigint]
        │   Estimates: {rows: 1 (8B)}
        │   Output: 8 rows (72B), PeakMemory: 0B (0.00%), WallTime: 212.92us (3.99%)
        │   count_0_1 := count(*)
        └─ TableScan[adb:AdbTableHandle{schema=test4dmp, tableName=test, partitionColumnHandles=[id]}]
               Outputs: []
               Estimates: {rows: 4 (0B)}
               Output: 0 rows (0B), PeakMemory: 0B (0.00%), WallTime: 4.76ms (89.12%)
               Input avg.: 0.00 rows, Input std.dev.: ? %
    Fragment 2 [adb:AdbPartitioningHandle{schema=test4dmp, tableName=test, dimTable=false, shards=4, tableEngineType=Cstore, partitionColumns=id, prunedBuckets= empty}]
        Output: 4 rows (36B), PeakMemory: 0B, WallTime: 102.00ns, Input: 0 rows (0B); per task: avg.: 0.00 std.dev.: 0.00
        Output layout: [count_0_1]
        Output partitioning: SINGLE []
        Aggregate(PARTIAL)
        │   Outputs: [count_0_1:bigint]
        │   Estimates: {rows: 1 (8B)}
        │   Output: 8 rows (72B), PeakMemory: 0B (0.00%), WallTime: 252.23us (0.12%)
        │   count_0_1 := count(*)
        └─ ScanFilterProject[table = adb:AdbTableHandle{schema=test4dmp, tableName=test, partitionColumnHandles=[id]}, filterPredicate = (`test4dmp`.`length`(`string_test`) = BIGINT '1')]
               Outputs: []
               Estimates: {rows: 9999 (312.47kB)}/{rows: 9999 (312.47kB)}/{rows: ? (?)}
               Output: 0 rows (0B), PeakMemory: 0B (0.00%), WallTime: 101.31ms (49.84%)
               string_test := AdbColumnHandle{columnName=string_test, type=13, isIndexed=true}
               Input: 9999 rows (110.32kB), Filtered: 100.00%
  2. Bad SQL memory usage

    You can view PeakMemory of each stage to find the resource consumption problem. Excluding the Disaster Broadcast scenario, PeakMemory is high, because joined data bloats, the volume of the data in the left joined data table is large, or the scanned data volume of TableScan is large. You must limit the values by adding conditions from the business perspective. You can also view the PeakMemory percentage of each operator to search for the operator that has the largest resource consumption.

  3. Data skew of source

    In the following example, "skewed" is a table that has skewed partitions. The partition key is "id", and only one value exists. "customer" is a normal table, and "skewed" and "customer" are joined. The following code shows the output plan of the partial execution plan analysis. When stage 3 scans the "skewed" table , you can view “ per task: avg.: 1666.50 std.dev.: 3726.41”. The standard deviation is large, and the stage is a TableScan. You can check whether the table is skewed. You must confirm whether the partition key needs to be adjusted.

    Fragment 3 [adb:AdbPartitioningHandle{schema=mytest, tableName=skewed, dimTable=false, shards=8, tableEngineType=Cstore, partitionColumns=id, prunedBuckets= empty}]
        Output: 9999 rows (87.88kB), PeakMemory: 0B, WallTime: 20.00ns, Input: 9999 rows (87.88kB); per task: avg.: 1666.50 std.dev.: 3726.41
        Output layout: [id]
        Output partitioning: BROADCAST []
        TableScan[adb:AdbTableHandle{schema=mytest, tableName=skewed, partitionColumnHandles=[id]}]
            Outputs: [id:bigint]
            Estimates: {rows: 9999 (78.12kB)}
            Output: 19998 rows (175.76kB), PeakMemory: 0B (0.00%), WallTime: 19.98ms (0.17%)
            id := AdbColumnHandle{columnName=id, type=5, isIndexed=true}