Query Profile diagnostic suggestions

Prerequisites

The query profile feature is enabled. For more information, see Enable query profile.

Using query profile diagnostic suggestions

Viewing diagnostic suggestions for SQL statements

On the Execution Details page, if diagnostic suggestions are available at the SQL statement level, they are displayed in the Execution Overview panel on the right. For more information, see Diagnostic suggestions for SQL statements.

At the bottom of the Overview panel, the message "Operators with diagnostic suggestions exist" is highlighted with an orange border. Click Details to view diagnostics for specific operators. The execution plan tree on the left highlights operator nodes with performance issues, such as CONNECTOR_SCAN, in pink.

Viewing diagnostic suggestions for SQL operators

On the Execution Details page, select an operator node with a yellow mark in its upper-right corner. The panel on the right displays the diagnostic suggestions for the operator. For more information, see SQL operator diagnostic suggestions.

For example, after you select a HASH_JOIN operator node, the Diagnostic result section at the bottom of the panel on the right lists specific optimization suggestions, such as Join result expansion (the input row count is 0 and the output row count is 19,000 K; check the join conditions and add more conditions to avoid join result expansion) and Join should use broadcast (add the [broadcast] hint after the JOIN keyword).

Diagnostic suggestions for SQL statements

When Serverless StarRocks analyzes query profile metrics, it analyzes the metrics at each layer from top to bottom. On the Execution Overview page, in addition to operator-level metrics, the system summarizes these analyses as diagnostic suggestions for the entire SQL statement, offering direct guidance for performance tuning.

Diagnostic suggestions at the SQL statement level include the following:

Whether metrics in the Query Profile Summary section are normal. For example, whether the CollectProfileTime metric is too large.
Whether metrics in the Query Profile Planner section are normal. For example, whether the CoordDeliverExec metric is normal.
Whether fragment metrics are normal. For example, whether fragment instances have skewed execution times.
A summary of the diagnostic suggestions for each operator identified during the analysis.

SQL operator diagnostic suggestions

Serverless StarRocks uses models based on metrics like wall time and CPU execution cost to identify operators that require diagnosis. It then interprets query profile metrics by operator type and provides diagnostic suggestions. In most cases, operators with a higher share of wall time in the overall SQL execution time deliver greater optimization gains. However, because operators in the execution plan tree are interdependent, even changes to operators with a small time share can trigger a re-optimization of the plan tree structure and indirectly improve overall SQL performance.

Diagnostic suggestions for query profile operators include general and operator-specific suggestions. This topic lists some common suggestions. When you use query profile diagnostics, the system also returns a summary of the diagnostic results, suggestions, and their detailed reasons.

General operator diagnostic suggestions

General operator diagnostic suggestions apply to all operators.

High operator execution time ratio

The system reports High execution time ratio of an operator if an operator instance's maximum execution time exceeds a configured percentage of the total SQL execution time, and the diagnostic engine cannot pinpoint a more specific cause. Focus on and optimize operators that have a high execution time ratio.

Scan operator diagnostic suggestions

Diagnostic suggestion	Description	Actions
Data skew	StarRocks data is unevenly distributed across storage nodes, causing some nodes to scan more data during reads and thereby increasing query latency.	Check and optimize the bucket key settings to ensure a more even data distribution.
I/O skew	When multiple instances of a scan operator read data, some instances take much longer than others.	Consider the following troubleshooting steps: Resources: Check whether a node has significantly higher I/O utilization than others, or whether the I/O thread pool is saturated. Data model: Check whether the queried data is unevenly distributed across nodes.
Ineffective data filtering during scan	This issue occurs when a scan operator reads a large amount of data, but the volume is not significantly reduced before being passed to downstream operators. This indicates that the data was not effectively filtered.	StarRocks provides multiple ways to reduce the amount of data scanned by a scan operator, such as an index, predicate pushdown, and Join Runtime Filter. These methods can improve scan filtering efficiency. Consider the following troubleshooting steps: Check whether the table schema is reasonable. Check whether the sort key is appropriate. Check whether you need to add an index. Check whether query conditions contain functions, which can prevent the conditions from being used to filter data.

Join operator diagnostic suggestions

Diagnostic suggestion	Description	Actions
Join result expansion	Typically, a join operator outputs a number of rows less than or equal to its input. A significantly larger output is considered a join result expansion. This issue often occurs when join conditions are missing, causing a Cross Join, or when incorrect join conditions create a 1-to-N relationship between the two tables. It can also happen when statistics are missing or become outdated after data changes, which causes the optimizer to choose an incorrect plan.	Consider the following troubleshooting steps: Optimize join conditions: Check whether join conditions are missing or do not match your business logic. Add necessary conditions for accurate matching to avoid join result expansion. Optimize the multi-table join order: If you are joining multiple tables, check whether the join order is correct. If not, set the session parameter `disable_join_reorder` to `true` to manually control the join order in the query.
Inappropriate selection of build table	In a join operator, the build phase creates a hash table in memory. An overly large build table consumes excessive memory. This usually happens because the optimizer selects the wrong build table.	Consider the following troubleshooting steps: Check whether statistics are collected or have become outdated. Set the session parameter `disable_join_reorder` to `true` to manually control the join order in the query.
Inappropriate use of broadcast join	When joining a large table with a much smaller one, a broadcast join can broadcast the small table to the nodes that hold the large table. This avoids data redistribution of the large table and reduces network overhead. However, in some cases, the optimizer misestimates table sizes due to incorrect statistics, causing a large table to be chosen for a broadcast join. This increases network and compute costs.	Add the `[shuffle]` hint after the JOIN keyword to prevent a broadcast join.

Aggregate operator diagnostic suggestions

Diagnostic suggestion	Description	Actions
Inefficient local aggregation	During aggregation, each compute node usually performs local aggregation to produce a smaller intermediate result and then sends it to other nodes. This reduces network transfer. In some cases, local aggregation fails to significantly reduce data volume. This ineffective aggregation consumes compute resources without reducing network transfer.	If the overall aggregation (including the non-local aggregation stages) takes a long time, disable two-stage aggregation by setting the session parameter `new_planner_agg_stage=1`.

References

For details on the structure and metrics of a query profile, see Query profile.