Release Notes
Product Announcements
EXPLAIN <SELECT_STATMENT>explain select * from t1 left join t2 on t1.id = t2.id;
+---------------------------------------------------------------------------------+| query plan |+---------------------------------------------------------------------------------+| ============================================ || |ID|OPERATOR |NAME|EST.ROWS| || -------------------------------------------- || |0 |HASH JOIN | |12500.00| || |1 |├─TABLE FULL SCAN (B) |t2 |10000.00| || |2 |└─TABLE FULL SCAN (P) |t1 |10000.00| || -------------------------------------------- || Details: || ------------------------------------- || 0 - JOIN TYPE: left outer join, EQUAL: [eq(singleton.t1.id, singleton.t2.id)] || 1 - STORAGE: LIBRASTORE, BLOCK OFFSET: 1 || 2 - STORAGE: LIBRASTORE, BLOCK OFFSET: 1 |+---------------------------------------------------------------------------------+
Operator Name | Operator Introduction |
SORT | The SORT operator is used to sort the input data. |
TOPN | If an ORDER BY clause is followed by a LIMIT clause, the optimizer will further optimize the execution plan, generating a TOP-N SORT operator and using heap sort to select the TOP-N data. |
LIMIT | The LIMIT operator is used to restrict the number of rows in data output, same as the LIMIT operator feature in MySQL. |
FILTER | The FILTER operator is used to filter data based on specified predicate conditions, commonly appearing in WHERE/HAVING/ON clauses. |
HASH JOIN | HASH JOIN is used to perform JOIN operations on large datasets. The optimizer uses two tables from a dataset. The smaller table is used to build a HASH table in memory based on JOIN conditions, and then the database scans the larger dataset and probes the HASH table to find rows that meet the JOIN conditions. |
COLUMN READ | Late materialization operator. The read-only analysis engine supports pushing down partial filter conditions to the TableScan operator, which means first scanning the column data related to the filter conditions, filtering to get the matching rows, and then scanning other column data of these rows for subsequent computation, so as to reduce the IO scans and computation amount of data processing. |
TABLE FULL SCAN | Performs a full-table scan on the target table. |
UINION | The UNION operator is used to perform a union operation on the result sets of two queries. |
WINDOW FUNCTION | The WINDOW FUNCTION operator is used to implement the analytic functions (also called window functions) in SQL and obtain the operation results for related rows within the window. The window function can return multiple rows per group, where each row shows the results of logical operations based on the window. |
HASH GROUP BY | The GROUP BY operator is mainly used for grouping and aggregation operations in SQL. Common aggregation functions (SUM/MAX/MIN/AVG/COUNT/STDDEV) are completed by assigning a GROUP BY operator. |
PROJECTION | The Projection operator corresponds to the SELECT list in SQL statements. Its feature is mapping each input data entry as new output data. |
EXCHANGE RECEIVER | The data receiver operator is used for receiving data during data exchange across compute nodes when MPP queries are executed. |
EXCHAGE SENDER | The data sender operator is used for sending data during data exchange across compute nodes when MPP queries are executed. |
Operator Name | Detail Information |
SORT | ORDER BY KEY: The sort key used by the sort operator. |
TOPN | ORDER BY KEY: The sort key used by the sort operator. OFFSET: The offset specified by the Limit operation. COUNT: The number of preserved rows specified by the Limit operation. |
LIMIT | OFFSET: The offset specified by the Limit operation. COUNT: The number of preserved rows specified by the Limit operation. |
FILTER | CONDITIONS: The predicate condition used by the FILTER operator for filtering data. |
HASH JOIN | JOIN TYPE: The type of the current JOIN, such as inner join, left outer join, semi join, etc. NON EQUAL: Whether the current JOIN is a Cartesian product. If this field is missing, it means that the current JOIN is not a Cartesian product. EQUAL: The Equal condition used for joining two tables. OTHER COND: The Not Equal condition used for joining two tables. Additionally, in the above EXPLAIN results, the operator names with the IDs 1 and 2 are followed by (B) and (P) tags. These tags indicate the Build and Probe sides during the HASH JOIN operation. B stands for Build, and P stands for Probe. |
COLUMN READ | COLUMN READ: The names of columns read by late materialization. |
TABLE FULL SCAN | STORAGE: The underlying storage types read, currently supporting two types, LIBRASTORE and TDSQL. BLOCK OFFSET: The number of the query block where the current table is located in the entire SQL statement, used to assist with Hint usage. |
UINION | N/A |
WINDOW FUNCTION | WINDOW FUNC DESC: The name of the window function. PARTITION BY: The partition key. ORDER BY: The sort key used for sorting. FRAME: The window definition of the window function. |
HASH GROUP BY | GROUP BY: The Group By key specified for executing the aggregation function. AGG FUNCS: The aggregation function specified in SQL. |
PROJECTION | EXPRS: The expression list executed by the PROJECTION operator, mainly including the cast function and various scalar functions. |
EXCHANGE RECEIVER | N/A |
EXCHAGE SENDER | ExchangeType: The method used for data exchange: PASS: Sends data of multiple nodes to one node. BCJ: Broadcasts data of one node to multiple nodes. For example, during JOIN, the build table data is broadcast to each node for the JOIN operation. HASH: Shuffles the data using a HASH function and then distributes it to each node. For example, it is used to shuffle the data of both tables for redistribution during JOIN. HASH (BY PARTITION): When two tables are joined and the join key of one table is the partition key, the other table is shuffled according to the distribution mode of the table with the join key as the partition key. |
/* HASH_JOIN_PROBE(t1) */ indicates that the optimizer will force using the t1 table as the Probe table in the HASH JOIN.Was this page helpful?
You can also Contact sales or Submit a Ticket for help.
Help us improve! Rate your documentation experience in 5 mins.
Feedback