Slow Query Analysis Tools
After you identify a slow query, compare its execution plan, operator metrics, and cluster resource usage. Use the result to distinguish a poor plan from an execution bottleneck or resource pressure.
Prepare for analysis: Before starting the analysis, confirm that you have the following:
- You have identified a specific slow query.
- You can connect to the warehouse and execute
EXPLAINfor the target query. - You can obtain a query profile for the statement.
- You can view the warehouse and cluster Metrics for the incident time range.
SQL execution stages and bottlenecks
The execution of a SQL statement is divided into two stages:
- Plan generation stage: responsible for generating the execution plan.
- Plan execution stage: responsible for executing the specific plan.
Problems in either stage can lead to performance bottlenecks:
- Poor plan: An unsuitable scan or join can perform unnecessary work.
- Execution bottleneck: Operators can spend time computing, waiting for data, or waiting for workers.
- Resource pressure: Concurrent workloads, cache misses, or I/O constraints can slow execution.
Choose an analysis tool
| Bottleneck Type | Recommended Analysis Tool | Main Purpose |
|---|---|---|
| Plan-layer bottleneck | EXPLAIN | View the SQL execution plan and locate plan-generation issues |
| Execution-layer bottleneck | Query Profile | View detailed runtime execution information for each operator |
| Resource bottleneck | VeloDB Cloud Metrics | Compare CPU, memory, cache, storage I/O, and network usage during query execution. |
The following sections introduce these three types of tools.
Analyze plan bottlenecks with EXPLAIN
Definition
An execution plan is a description of the specific way and process by which a SQL statement is executed. For example, for a SQL statement that joins two tables, the execution plan shows the access methods of the two tables, the join method, and the join order.
Purpose
Run EXPLAIN for the target statement and inspect its scan, join, aggregation, and exchange operators. Check whether row estimates, filters, and join choices match the query and data.
EXPLAIN variants
Choose an EXPLAIN variant for the question you need to answer:
| Explain Type | Output Content | Applicable Scenario |
|---|---|---|
EXPLAIN VERBOSE | Final physical plan | View the actual physical plan delivered to BE |
EXPLAIN ALL PLAN | Logical plans at each stage | Track the evolution of logical plans |
EXPLAIN MEMO PLAN | Cost-based optimization process plan | Analyze decisions made by the CBO optimizer |
EXPLAIN SHAPE PLAN | Plan shape | Quickly view the skeleton and structure of a plan |
See the EXPLAIN reference for syntax.
Common plan bottlenecks
By analyzing the Explain output, you can quickly locate the following common plan-layer bottlenecks:
- Filter not pushed down to the base table: Data is not filtered in advance, resulting in too much data participating in the computation.
- Join condition not derived to the other side: In an inner equi-join between two tables, the filter condition on one side is not derived to the other side, so the data on the other table is not filtered in advance.
For cases of plan-layer tuning using EXPLAIN, see the Plan Tuning chapter.
Analyze execution bottlenecks with Query Profile
Definition
A Profile is a record of the detailed time consumption and runtime metrics of each operator during the actual execution of a SQL, used to locate execution-layer performance bottlenecks.
Difference from EXPLAIN
Explain describes the planning of SQL execution (for example, a hash join is planned with a chosen build side and probe side). Profile describes the actual process of SQL execution (for example, how long the build takes and how long the probe takes).
Profile structure
A Profile file contains the following main parts:
- Basic query information: including ID, time, database, etc.
- SQL statement and execution plan.
- FE time consumption: including Plan Time, Schedule Time, etc.
- Execution time consumption of each BE operator: including Merged Profile and Execution Profile.
The detailed information on the execution side is mainly in the last part. The following sections focus on the execution information provided by the Profile.
Merged profile
The Merged Profile provides the core metrics of each operator aggregated across all concurrent instances, making it easy to quickly locate bottlenecks. In the query engine, each operator executes concurrently according to the concurrency level set by the user, and the Merged Profile calculates the Max, Avg, and Min values for each metric.
Example 1: Read merged exchange metrics
Inspect aggregate exchange counters
EXCHANGE_OPERATOR (id=4):
- BlocksProduced: sum 0, avg 0, max 0, min 0
- CloseTime: avg 34.133us, max 38.287us, min 29.979us
- ExecTime: avg 700.357us, max 706.351us, min 694.364us
- InitTime: avg 648.104us, max 648.604us, min 647.605us
- MemoryUsage: sum , avg , max , min
- PeakMemoryUsage: sum 0.00 , avg 0.00 , max 0.00 , min 0.00
- OpenTime: avg 4.541us, max 5.943us, min 3.139us
- ProjectionTime: avg 0ns, max 0ns, min 0ns
- RowsProduced: sum 0, avg 0, max 0, min 0
- WaitForDependencyTime: avg 0ns, max 0ns, min 0ns
- WaitForData0: avg 9.434ms, max 9.476ms, min 9.391ms
Description of core metrics:
| Metric Name | Metric Meaning |
|---|---|
| BlocksProduced | Number of Data Blocks produced |
| CloseTime | Time spent by the operator in the close phase |
| ExecTime | Total execution time of the operator across all phases |
| InitTime | Time spent by the operator in the Init phase |
| MemoryUsage | Memory usage of the operator during the execution phase |
| OpenTime | Time spent by the operator in the Open phase |
| ProjectionTime | Time spent by the operator on projection |
| RowsProduced | Number of rows returned by the operator |
| WaitForDependencyTime | Time the operator waits on its own execution dependencies |
WaitForDependencyTime varies by operator. For example, in the EXCHANGE_OPERATOR above, the dependency is the upstream operator sending data via RPC, so this metric actually measures the time spent waiting for the upstream operator to send data.
Execution profile
The Execution Profile shows the detailed metrics of a specific concurrent instance. Compared with the Merged Profile, it is more fine-grained and includes operator-specialized metrics.
Example 2: Read one exchange instance
Inspect detailed exchange counters
EXCHANGE_OPERATOR (id=4):(ExecTime: 706.351us)
- BlocksProduced: 0
- CloseTime: 38.287us
- DataArrivalWaitTime: 0ns
- DecompressBytes: 0.00
- DecompressTime: 0ns
- DeserializeRowBatchTimer: 0ns
- ExecTime: 706.351us
- FirstBatchArrivalWaitTime: 0ns
- InitTime: 647.605us
- LocalBytesReceived: 0.00
- MemoryUsage:
- PeakMemoryUsage: 0.00
- OpenTime: 5.943us
- ProjectionTime: 0ns
- RemoteBytesReceived: 0.00
- RowsProduced: 0
- SendersBlockedTotalTimer(*): 0ns
- WaitForDependencyTime: 0ns
- WaitForData0: 9.476ms
For example, LocalBytesReceived is specific to EXCHANGE_OPERATOR. Available counters depend on the operator, profile level, and Core version.
PipelineTask timing
In the query engine, a PipelineTask is composed of multiple operators. To analyze the execution time of a PipelineTask, focus on the following three aspects:
| Focus Area | Description |
|---|---|
| ExecuteTime | The actual execution time of the PipelineTask, approximately equal to the sum of ExecTime of all operators in the task |
| WaitWorkerTime | The time the task waits for an execution Worker, mainly determined by the cluster load |
| Time waiting on execution dependencies | The sum of all operator dependency wait times (a task can be executed only when all dependencies are satisfied) |
Example 3: Read PipelineTask timings
Compare work and dependency wait times
PipelineTask (index=1):(ExecTime: 4.773ms)
- ExecuteTime: 1.656ms
- CloseTime: 90.402us
- GetBlockTime: 11.235us
- OpenTime: 1.448ms
- PrepareTime: 1.555ms
- SinkTime: 14.228us
- WaitWorkerTime: 63.868us
DATA_STREAM_SINK_OPERATOR (id=8,dst_id=8):(ExecTime: 1.688ms)
- WaitForDependencyTime: 0ns
- WaitForBroadcastBuffer: 0ns
- WaitForRpcBufferQueue: 0ns
AGGREGATION_OPERATOR (id=7 , nereids_id=648):(ExecTime: 398.12us)
- WaitForDependency[AGGREGATION_OPERATOR_DEPENDENCY]Time: 10.495ms
This task contains two operators (DATA_STREAM_SINK_OPERATOR and AGGREGATION_OPERATOR). DATA_STREAM_SINK_OPERATOR has two dependencies (WaitForBroadcastBuffer and WaitForRpcBufferQueue), and AGGREGATION_OPERATOR has one dependency (AGGREGATION_OPERATOR_DEPENDENCY). The time distribution of this task is as follows:
| Time Type | Value | Description |
|---|---|---|
| Total execution time | 1.656 ms | Approximately equal to the sum of ExecTime of the two operators |
| Time waiting for Worker | 63.868 us | The current cluster load is not high, and a Worker executes the task immediately after it is ready |
| Time waiting on execution dependencies | 10.495 ms | Equal to the sum of all dependency wait times |
For cases of execution-layer tuning using Profile, see the Execution Tuning chapter.
Compare cluster resource metrics
Use Metrics for the same cluster and time range as the query. Compare CPU and memory utilization with disk-cache hit rate, remote-storage throughput, I/O utilization, and network throughput. These measurements help explain high worker wait time or slow scans without requiring access to managed engine hosts.
For BYOC integration with your own dashboards, see BYOC Monitoring Integrations. If operator metrics and resource charts do not explain the issue, share the query ID, profile, and time range with VeloDB Cloud support.
FAQ
Q1: Among Explain, Profile, and cluster metrics, which should you use first?
Investigate in order from highest to lowest priority: first look at Explain to rule out plan issues. Once the plan is reasonable, look at Profile to locate the slow operator. If the operator's own time consumption is reasonable but the overall execution is still slow, then look at the cluster metrics.
Q2: Should you choose Merged Profile or Execution Profile?
First look at Merged Profile to quickly identify time consumption differences among operators. When you suspect that a particular concurrent instance is abnormal or need to view operator-specialized metrics, then look at Execution Profile.
Q3: The PipelineTask is slow overall, but ExecuteTime is not high. What is the reason?
Usually it is high WaitWorkerTime (high cluster load) or high time waiting on dependencies (the upstream operator does not produce data in time). You need to use the dependency name to locate the upstream bottleneck.
Q4: WaitForDependencyTime is 0, but WaitForData0 is very high?
WaitForData0 is the specific dependency for EXCHANGE_OPERATOR waiting for upstream RPC data. A high value of this metric usually means that the upstream operator produces data slowly, and you should further investigate the upstream operator.
Summary
Performance analysis tools are an important prerequisite for quickly locating performance bottlenecks.
- EXPLAIN: locates plan-layer bottlenecks.
- Query Profile: locates execution-layer bottlenecks (Merged Profile + Execution Profile + PipelineTask time).
- Cluster metrics: assist in locating bottlenecks at the hardware and operating system layers.
Compare all three sources before selecting a tuning change.