tencent cloud

TDSQL Boundless

DocumentaçãoTDSQL BoundlessUser GuideRead-Only Analysis InstanceExecuting QueriesExecuting SQL and Viewing the Execution Plan

Executing SQL and Viewing the Execution Plan

Download
Modo Foco
Tamanho da Fonte
Última atualização: 2026-07-08 17:25:19
This document describes how to execute the EXPLAIN statement in TDSQL Boundless read-only analysis instances and how to interpret the returned execution plan, helping you evaluate query performance, identify execution bottlenecks, and guide optimization.

Scenarios

The EXPLAIN statement is used to view the execution plan of a query. The read-only analysis instance is compatible with the MySQL protocol. You can connect to the read-only analysis instance using any MySQL client and then execute the EXPLAIN statement. The returned plan describes the operator combination selected by the optimizer for the SQL, the data flow method, and the cost estimation. It can be used for:
Assess whether the SQL query uses the expected Join order, Join algorithm, and scan method (full column scan or remote row read).
Identify potential risk points such as data skew, broadcast amplification, and Cartesian products.
Intervene in and optimize the execution plan using Optimizer Hint.

Prerequisites

The read-only analysis instance has been purchased and enabled.
You have connected to the read-only analysis instance using a MySQL client.
You have obtained query permissions for the target database and table.

EXPLAIN Syntax

The read-only analysis instance supports the following three common forms.
Syntax
Description
Whether SQL Is Actually Executed
EXPLAIN <SQL>
View the execution plan in the default (BRIEF) format.
No
EXPLAIN FORMAT='<format>' <SQL>
Specifies the output format. For common format values, see the Output Format section.
No
EXPLAIN ANALYZE <SQL>
Executes the SQL statement and includes the actual number of rows, actual cost, and time consumption for each operator in the plan.
Yes
Attention:
The EXPLAIN ANALYZE statement actually executes the query. When the query is executed on a large table, estimate the resource consumption to avoid impacting online services.
The EXPLAIN statement is primarily used to view the execution plan of SELECT statements.

Output Format

The FORMAT clause specifies the output format of the EXPLAIN statement. Common values are listed in the table below. For daily use, brief is recommended. Other values are typically used for diagnostics or tool integration scenarios.
FORMAT Value
Output Format
Applicable Scenarios
brief (Default)
Table, columns: ID, OPERATOR, NAME, EST.ROWS, DETAILS
Suitable for daily viewing of execution plans with concise column widths for readability.
row
Table, columns: id, operator, rows estimate, cost estimate, task type, tables visited, details
A traditional output equivalent to brief, with all column names in lowercase.
verbose
Table, with the same columns as row
Shares the same structure as row and is used when cost estimation is focused on.
tree
Tree indentation format
Intuitively view the hierarchical relationships of operators.
detail
Table, with the same columns as row
Contains more complete field details.
hint
Hint text
Outputs applicable Hint suggestions for the current query.
Note:
In addition to the common values mentioned above, the code level also supports values such as dot, json, traditional, true_card_cost, binary, and detail_no_idsuffix, which are primarily used for toolchain integration or internal debugging. For daily use, it is recommended to prioritize using brief.

EXPLAIN ANALYZE Output

EXPLAIN ANALYZE outputs an additional row of stage time statistics above the plan and expands the operator column to eight columns. The stage time format is as follows:
TotalTime (ns): <Total Time> ParseTime (ns): <Parse Time> CompileTime (ns): <Compile Time> OptimizationTime (ns): <Optimization Time> WaitTimestampTime (ns): <Wait Timestamp Time>
The meanings of the eight columns are as follows.
Column Name
Description
ID
Sequence number of the operator in the plan.
OPERATOR
Operator type.
NAME
The object on which the operator acts, such as table name, Join type, and so on.
EST.ROWS
Estimated number of output rows by the optimizer.
ACT.ROWS
Actual number of output rows.
ACT.OUTPUTSIZE
Actual output size in bytes.
EST.COST
Estimated cost by the optimizer.
ACT.COST
Actual cost (including the proportion of execution time).
By comparing EST.ROWS with ACT.ROWS, you can quickly identify execution plan issues caused by statistical information deviation or selectivity estimation errors.

Interpreting Execution Plan Results

Output Column Descriptions (Default BRIEF Format)

Column Name
Description
ID
Sequence number of the operator in the plan, numbered in depth-first order from root to leaf.
OPERATOR
Operator type, such as HASH JOIN, TABLE FULL SCAN, EXCHANGE SENDER, and so on.
NAME
The object on which the operator acts, such as table name (TABLE FULL SCAN displays the table name), Join type abbreviation, and so on.
EST.ROWS
Estimated number of output rows by the optimizer based on statistics.
DETAILS
Detailed information of the operator, such as filter conditions, grouping keys, Join equality conditions, sorting keys, Runtime Filter, and so on. The fields vary for each operator. For details, see the Operator Details section.

ID Column Build/Probe Suffix

On the two child nodes of the HASH JOIN operator, the ID column is appended with a suffix to identify the role that child node plays in the Hash Join.
Suffix
Description
(B)
The Build side. Hash Join first scans the data from the Build side and builds a hash table.
(P)
The Probe side. Hash Join uses each row from the Probe side to probe the hash table and obtain matching results.
The Build side is typically chosen as the one with a smaller data volume to reduce hash table memory overhead. To specify it manually, you can use the HASH_JOIN_BUILD and HASH_JOIN_PROBE hints. For details, see the Optimizer Hint section.

OPERATOR Overview

The table below lists common operators in the analysis engine. Operators marked with an asterisk (*) appear only in specific scenarios, such as remote reading of TDSQL row-store data, CTE, or special optimization paths. Not seeing these asterisked operators in daily queries is normal.
Category
Operator
Description
Scan
TABLE FULL SCAN
Performs a full table scan on columnar data. It is the most common scan operator in Libr.
Scan
COLUMN READ
Reads columnar data on demand. It is a columnar read operator that fetches only the columns required for computation.
Scan
REMOTE READER*
A remote read operator. It appears when SQL involves TDSQL row-based data, pushes down the sub-plan to TDSQL row-based storage for execution, and then returns the result.
Scan
INDEX FULL SCAN*
Performs a full index scan. It usually appears in the REMOTE READER subtree.
Scan
INDEX RANGE SCAN*
Performs an index range scan. It usually appears in the REMOTE READER subtree.
Join
HASH JOIN
Performs a hash join. Depending on the join type, it can be displayed as HASH JOIN, HASH OUTER JOIN, HASH RIGHT OUTER JOIN, HASH SEMI JOIN, or HASH NA-SEMI JOIN (NA stands for Null-Aware).
Join
MERGE JOIN*
Performs a merge join. It requires that inputs from both sides be ordered by the join key.
Aggregation
HASH GROUP BY
Performs grouping and aggregation based on a hash table.
Aggregation
MERGE GROUP BY*
Performs streaming aggregation on ordered input.
Sorting and throttling
SORT
Sorting operator.
Sorting and throttling
TOPN
Retrieves the top N rows. It is equivalent to a sorting operation with LIMIT and is generally more efficient than SORT + LIMIT.
Sorting and throttling
LIMIT
Limits the number of rows returned.
Projection and filtering
PROJECTION
Projection operator. It outputs a list of expressions.
Projection and filtering
FILTER
Filter operator. It corresponds to conditions in SQL such as WHERE and HAVING.
Set and window
UNION
Merges multiple input streams.
Set and window
WINDOW FUNCTION
Window function operator.
Distributed exchange
EXCHANGE SENDER
The MPP data exchange sender sends data to upstream nodes.
Distributed exchange
EXCHANGE RECEIVER
The MPP data exchange receiver receives data from downstream nodes.
Distributed exchange
LOCAL SHUFFLE*
Redistributes data within a node using multiple threads.
Materialization and CTE
MATERIAL*
Materializes intermediate results.
Materialization and CTE
RECURSIVE UNION*
Recursive CTE.
Materialization and CTE
CTE MATERIAL*
CTE materialization result.
Materialization and CTE
CTE FULL SCAN*
Reads materialized CTE results.

OPERATOR Details (DETAILS Column)

The table below summarizes the fields that common operators may output in the DETAILS column. Which specific fields appear depends on the actual configuration of that operator.

TABLE FULL SCAN

Field
Description
STORAGE
The storage engine type. Its value is LIBRASTORE (columnar analytics engine) or TDSQL (TDSQL row store).
TABLE NAME
The name of the table being scanned.
TABLE ALIAS
The alias specified for the table in the SQL statement, if any.
ACCESS COLUMN
The list of column names actually read. Libra is a columnar store and reads only the columns required for computation.
BLOCK OFFSET
The offset information of columnar data blocks is automatically managed by the system, and users do not need to be concerned about it.
Partitions
The list of partitions actually hit, if any.
KEEP ORDER
Whether to keep ordered output.
DESC
Whether to scan in reverse order.
COMPLETE PIPELINE
Whether to perform a complete pipeline scan.
PROBE RUNTIME FILTERS
The list of Runtime Filters on the Probe side. A Runtime Filter is a filter constructed by Hash Join on the Build side and pushed down to the scan or upper-level operators on the Probe side to filter data in advance, thereby reducing subsequent computational load.

HASH JOIN

Field
Description
EQUAL
A list of equality join conditions, for example, [eq(t1.a, t2.a)].
OTHER COND
Other join/filter conditions.
BUILD RUNTIME FILTERS
A list of Runtime Filters constructed on the Build side, which is pushed down to the scan or Filter operators on the Probe side.
PROBE RUNTIME FILTERS
A list of Runtime Filters from the upstream Probe side (if any).
Note:
The Hash Join operator itself does not output the JOIN TYPE field. The join type is directly reflected in the operator name, such as HASH JOIN, HASH OUTER JOIN, HASH SEMI JOIN, or HASH NA-SEMI JOIN. The NON EQUAL/JOIN TYPE fields are output only in the DETAILS section of the Apply (correlated subquery) operator.

EXCHANGE SENDER

Field
Description
EXCHANGE TYPE
Data exchange type. For its values and meanings, refer to the following table.
HASH COLS
The list of columns used for hash distribution when EXCHANGE TYPE is HASH.
PARTITION DISTRIBUTION
Distribution information based on partition distribution when EXCHANGE TYPE is HASH (BY PARTITION).
The possible values for EXCHANGE TYPE are as follows.
Value
Full Name
Meaning
PASS
PassThrough
Directly passes through data without redistributing it.
BCJ
Broadcast
Broadcast mode, which replicates data to all upstream nodes and is commonly used for small-table Broadcast Join.
HASH
HashPartition
Distributes data after data is hashed based on specified columns, commonly used for data redistribution in large-table Hash Join.
HASH (BY PARTITION)
Partition Key(HASH)
Distributes data based on the hash value of the partition key, causing data from the same partition to be gathered on the same node.

EXCHANGE RECEIVER

No additional fields are output by default. When the system cooperates with a sorting operator to implement ordered data exchange (N:M ordered exchange), the KEEP ORDER field is additionally output.

HASH GROUP BY and MERGE GROUP BY

Field
Description
group by
List of grouping columns.
funcs
List of aggregate functions, for example, count(*), sum(t.a).

SORT and TOPN

Field
Description
Sort Key List
List items in order of sorting priority, each of which can be accompanied by asc or desc.
OFFSET
The offset for TOPN.
COUNT
The number of records retrieved by TOPN.

LIMIT

Field
Description
offset
Number of rows to skip.
count
Number of rows returned.

FILTER

Output the filter condition list, for example [gt(t.a, 10)].

PROJECTION

Output the projection expression list, which primarily consists of cast expressions and various scalar function calls.

UNION

No additional fields are output by default. It outputs the merged information from all branches.

WINDOW FUNCTION

Output the window function definition, which includes the WINDOW FUNC DESCS (window function description), PARTITION BY columns, ORDER BY columns, window Frame definition, and more.

Intervening in Execution Plans via Optimizer Hints

When the default execution plan does not meet expectations, you can use Optimizer Hints in your SQL to guide the optimizer in selecting specific operators or an execution order. The Hint is written within the /*+ ... */ comment block that follows the SELECT keyword.

Common Hints Overview

Hint
Feature
HASH_JOIN(t1, t2)
Forces t1 and t2 to use Hash Join.
HASH_JOIN_BUILD(t)
Specifies t as the Build side of Hash Join.
HASH_JOIN_PROBE(t)
Specifies t as the Probe side of Hash Join.
MERGE_JOIN(t1, t2)
Forces the use of Merge Join.
INL_JOIN(t1, t2)
Forces the use of Index Nested Loop Join.
INL_HASH_JOIN(t1, t2)
Forces the use of Index Hash Join.
INL_MERGE_JOIN(t1, t2)
Forces the use of Index Merge Join.
USE_HASH(t1, t2)
Prompts the optimizer to prioritize Hash Join.
NO_USE_HASH(t1, t2)
Prompts the optimizer not to select Hash Join for t1 and t2.
PX_JOIN_FILTER(id [, type])
Forces the generation of a Runtime Filter for the specified Join (that is, a pre-filter constructed on the Build side).
NO_PX_JOIN_FILTER(id [, type])
Prohibits the generation of a Runtime Filter for the specified Join.
SEMI_JOIN_REWRITE()
Enables Semi Join rewrite optimization.
Note:
The analysis engine is a columnar OLAP engine. Some Hints designed for row-store index access, such as INL_JOIN, INL_HASH_JOIN, INL_MERGE_JOIN, and MERGE_JOIN, may not be adopted under the columnar execution path. Please refer to the actual output of EXPLAIN.

Hint Examples

For example, to forcibly designate the t2 table as the Probe side of a Hash Join:
SELECT /*+ HASH_JOIN_PROBE(t2) */ *
FROM t1 JOIN t2 ON t1.id = t2.id;
After executing EXPLAIN, you can observe in the ID column of the plan that the t2 subtree has the (P) suffix and the t1 subtree has the (B) suffix, thereby verifying that the Hint has taken effect.

Example: Interpreting the Execution Plan of a Two-Table Join Query

The following example demonstrates the approach to interpreting the execution plan for a typical two-table Join query.
EXPLAIN
SELECT t1.name, COUNT(*)
FROM t1 JOIN t2 ON t1.id = t2.id
WHERE t2.region = 'cn'
GROUP BY t1.name
ORDER BY COUNT(*) DESC
LIMIT 10;
The expected observable operator combinations (top-down) are as follows.
1. TOPN: It is equivalent to ORDER BY ... LIMIT 10, retaining only the top 10 rows while performing sorting operations.
2. PROJECTION: It projects the grouped results into the final output columns.
3. HASH GROUP BY: It groups the data by t1.name and performs COUNT(*).
4. EXCHANGE RECEIVER / EXCHANGE SENDER: It redistributes data by hashing t1.name to facilitate parallel grouping and aggregation.
5. HASH JOIN: It performs a join based on t1.id = t2.id. The ID columns of its child nodes are appended with the (B) and (P) suffixes, respectively.
6. TABLE FULL SCAN: It scans the t1 and t2 tables respectively. The STORAGE column displays LIBRASTORE, and the ACCESS COLUMN contains only the columns that actually participate in the computation. The t2 table may have the PROBE RUNTIME FILTERS field attached, indicating that the Runtime Filter built by the Hash Join on the Build side has been pushed down to this Probe side for pre-filtering.
By re-executing the SQL with EXPLAIN ANALYZE, you can compare whether EST.ROWS and ACT.ROWS are close, thereby determining the accuracy of the statistics. If a significant deviation exists, execute ANALYZE TABLE to collect the latest statistical information.

References

For more detailed descriptions of each operator, see the Runtime Filter Use Instructions.
Anterior: Window Function Use InstructionsPróximo: Optimizing SQL Execution Through HINT

Ajuda e Suporte

Esta página foi útil?

comentários