[HINT_EXPLAIN]

To monitor the real-time performance and execution order of a SQL query, you can print the query execution plan by adding "[HINT_EXPLAIN]" after the SQL keywords "select" or "exec". For example:

select [HINT_EXPLAIN] * from tb where id > 20

Note:

  • A SQL query with "[HINT_EXPLAIN]" returns a JSON string indicating the execution plan instead of the actual query result.

  • Currently, "[HINT_EXPLAIN]" does not work with "update" and "delete" statements.

"[HINT_EXPLAIN]" can be used in queries on both partitioned and non-partitioned tables. For a query on a partitioned table, the execution plan contains extra information about the map-reduce operation. This tutorial uses queries on a DFS table as examples to explain the attributes in the execution plan.

The outermost level of the JSON string consists of 2 root attributes, measurement and explain.

measurement

"measurement":"microsecond"

This indicates that the elapsed time (i.e., the values of the cost attribute) in the execution plan is measured in microseconds.

explain

The order of the sub attributes inside explain, from top to bottom, represents the execution order of the SQL query. Since a query may contain subqueries, explain can also be nested inside other attributes.

explain contains the following sub attributes:

  • rows: the number of records returned

  • cost: the time it takes to execute the query

{
  "measurement":"microsecond",
  "explain": {
      /* other attribute-value pairs */
      "rows": 20000,
      "cost": 100
  }
}

The execution of a distributed query has 3 phases: map → merge → reduce. The attributes corresponding to the 3 phases are usually nested inside the root attribute explain. For example:

{
  "measurement":"microsecond",
  "explain":{
      "from":{...},
      "map":{...},
      "merge":{...},
      "reduce":{...},
      "rows":10,
      "cost":23530
  }
}

The system optimizes queries where possible and the associated information is included in the "optimize" attribute in the execution plan. For example, in standalone mode, create a DFS table partitioned on the "date" column. Query all the records in a single partition:

select [HINT_EXPLAIN] * from t1 where date = 2022.01.01

The query is optimized and an "optimize" attribute is included in the execution plan.

{
  "measurement":"microsecond",
  "explain":{
      "from":{ ... },
      "optimize":{ ... },
      "rows":185,
      "cost":987
  }
}

from

from indicates the execution plan of the parsed "from" clause.

Depending on what follows the "from" keyword in the query, the from attribute may contain different sub attributes. Below are some of the possible scenarios:

(1) "from" is followed by a table object. For example, select [HINT_EXPLAIN] * from pt

"from": {
  "cost": 3  // Cost of the "from" clause
}

(2) The "from" clause contains a nested clause. For example, select [HINT_EXPLAIN] * from (select max(x) as maxx from loadTable("dfs://valuedb",`pt) group by month ) where maxx < 0.9994

"from": {
  "cost": 33571,  // Cost of the "from" clause
  "detail": {
  "sql": "select [98304] max(x) as maxx from loadTable("dfs://valuedb", "pt") group by month",  // The nested clause
  "explain": { ... } // "explain" for the nested clause
  }
}

In this example, there's a nested explain inside from showing the execution plan of the "select" subquery.

(3) "from" is followed by a table join clause. For example, select [HINT_EXPLAIN] * from lsj(pt1, pt2, "date")

"from": {
"cost": 3,  // Cost of the "from" clause
"detail": "materialize for JOIN" // Indicates that the clause involves a table join
}

Note: For the nested clause lsj(pt1, pt2, "date"), cost only indicates the cost to retrieve data from the source tables "pt1" and "pt2". At this stage, the tables are not yet joined.

where

For the TSDB engine, the required data will be filtered based on the index of the level file using the where condition. where may contain the following sub attributes:

  • TSDBIndexPrefiltering: Indicates the info of data and where condition during the prefiltering process.

    • blocksToBeScanned: The number of blocks to be scanned after the prefiltering.

    • matchedWhereConditions: The number of matched conditions in the WHERE clause that involve the sort keys, including equal and non-equal comparisons (excluding chained comparison), or the use of operators such as IN, BETWEEN, LIKE.

  • rows: The number of rows that satisfy the where condition.

  • cost: The cost of the WHERE clause.

"where": {
    "TSDBIndexPrefiltering": {
        "blocksToBeScanned": 0,
        "matchedWhereConditions": 1
    },
    "rows": 0,
    "cost": 416
}

map

In the map phase, the system identifies all the partitions (on local and remote nodes) involved in the query, then generates the corresponding subqueries and distributes the subqueries to all the partitions involved for parallel execution.

The map attribute may contain the following sub attributes:

  • generate_reshuffle_query: This attribute is unique to distributed joins, containing information about the "generate_reshuffle_query" operation. The operation takes place before the distributed join to redistribute data and store them continuously in memory based on the join column(s). If the join column(s) match the partitioning column(s), this operation is not performed. (Note that distributed joins are only available in the 2.00 version.)

  • partitions: Information about the partitions involved in this query. The sub attributes local and remote indicate the number of partitions on the local/remote nodes.

  • cost: The cost of the entire map phase. By the end of this phase, all subqueries are completed.

  • detail: The execution details of subqueries in the map phase:

    a. most: The information of the most time-consuming subquery.

    b. least: The information of the least time-consuming subquery.

"map": {
  "generate_reshuffle_query": {
      "cost": 2
  },
  "partitions": {
      "local": 10,
      "remote": 5,
  },
  "cost": 100,
  "detail": {
      "most": {
      "sql": "select time,id,value from pt [partition = /iot/1]",
      "explain": { ... }
      },
      "least": {
      "sql": "select time,id,value from pt [partition = /iot/4]",
      "explain": { ... },
      }
  }
}

optimize

optimize indicates how the query is optimized.

  • optimize: Query optimization information. It contains the following sub attributes:

  • field: Optimized clauses, such as "where", "join" and "group"; or optimization scenarios, such as "single partition query".

"optimize": {
  "cost": 3,
  "field": ["where", "join", "group"],
  "sql": "..."  // the optimized query
}

Below are some scenarios where the queries are optimized:

(1) When querying only a single partition, the query's execution plan of the map phase is as follows:

"map": {
  "partitions": {
      "local": 1,   // Or "0"
      "remote": 0,   // Or "1"
  },
  "cost": 100,
  "optimize": {
      "field": "single partition query",
      "sql": "...",
      "explain": { ... }
  }
}

As the query only involves one partition on a single node, merge is not required.

(2) context by + csort + limit

In this example, the query uses "context by" in conjunction with "csort" and "limit":

select * from pt where device in ["a", "b"] context by device csort time limit n

Here pt is a partitioned table (with one or multiple partitioning columns). The query will be optimized if the following conditions are satisfied:

  1. The "context by" column is filtered by "where". In this example, the device column is filtered by where device in ["a", "b""].

  2. The "csort" column (time in this example) is a partitioning column, and the partition type is VALUE or RANGE.

  3. "csort" and "context by" can only specify one column.

  4. The "context by" column is specified in the "select" clause.

map returns the following:

"map":{
  "optimize":{
      "cost":4,  // Cost of the optimization
      "field":"optimize for CONTEXT BY + CSORT + LIMIT: partial parallel execution."
  },
  "cost":1082  // Cost of the map phase
}

Based on the query statement and the table partitioning scheme, field returns either of the following:

  • optimize for CONTEXT BY + CSORT + LIMIT: partial parallel execution.

  • optimize for CONTEXT BY + CSORT + LIMIT: serial execution.

Note: If the query is optimized during execution, the execution plan may not contain the attributes merge and reduce.

merge

In the merge phase, the results of the subqueries assigned to each node are merged.

merge may contain the following sub attributes:

  • row: The total number of rows after the merge.

  • cost: The cost of the merge phase.

  • detail: Execution details of the subqueries in the merge phase. a. most: Information about the subquery returning the most rows. b. least: Information about the subquery returning the least rows.

"merge": {
  "row": 10000,
  "cost": 50,
  "detail": {
      "most": {  // the subquery returning the most rows
      "sql": "select time,id,value from pt [partition = /iot/6]",
      "explain": { ...}
      },
      "least": {
      "sql": "select time,id,value from pt [partition = /iot/9]",
      "explain": { ...}
      }
  }
}

reduce

The reduce phase combines the results of the subqueries, usually by performing one last query on the merged result. Note that there may or may not be a reduce phase in the execution plan.

reduce may contain the following sub attributes:

"reduce": {
  "sql": "...",  // the final query
  "explain": { ... }
}

Other attributes

In addition to the attributes described above at each stage of a distributed query, an execution plan may contain other SQL-related attributes.

groupBy

  • sortKey: Indicates whether the sortColumn is specified in the "group by" condition of the query. If the value is "true", there'll be no algo. Note that sortKey is only available in the 2.00 version.

  • algo: Indicates the grouping algorithm. It can be "hash", "vectorize" or "sort". When it is "sort", two additional fields are returned:

    • inplaceOptimization: Whether inplace optimization is conducted. Inplace optimization uses the same preset memory to store the results of all groups instead of frequent memory allocation for each temporary result, which reduces overheads and optimizes memory usage.

    • optimizedColumns: If inplaceOptimization returns true, this field returns the columns that were involved in inplace optimization. If the inplaceOptimization returns false, a null value is returned.

  • fill: Indicates the cost to implement the interpolation specified by the interval function.

"groupBy":{
  "sortKey":false,
  "algo":"hash",
  "cost":8
}

//  "group by" clause with the interval function
"groupBy": {
  "sortKey": false,
  "algo": "hash",
  "fill": {
      "cost": 23
  },
  "cost": 248
}

// When inplace optimization is applied
"groupBy": {
  "sortKey": false,
  "algo": "sort",
  "inplaceOptimization": true,
  "optimizedColumns": [
      "std_price0"
  ],
  "cost": 16422
}

contextBy

  • sortKey: Indicates whether the sortColumn is used in the "context by" clause.

"contextBy":{
  "sortKey":false,
  "cost":1994
}

Note: sortKey is only available in the 2.00 version.

join, csort, sort, pivotBy

These attributes only contain the cost sub attribute.

"join": {
"cost": 10
}

By printing the SQL execution plan and analyzing the time consumed in each part of query execution, it can help us optimize the SQL statement and improve the execution efficiency.