Skip to main content
VeloDB Cloud 26.x is based on Apache Doris 4.x. See Version Mapping.

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 EXPLAIN for 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:

  1. Plan generation stage: responsible for generating the execution plan.
  2. 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 TypeRecommended Analysis ToolMain Purpose
Plan-layer bottleneckEXPLAINView the SQL execution plan and locate plan-generation issues
Execution-layer bottleneckQuery ProfileView detailed runtime execution information for each operator
Resource bottleneckVeloDB Cloud MetricsCompare 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 TypeOutput ContentApplicable Scenario
EXPLAIN VERBOSEFinal physical planView the actual physical plan delivered to BE
EXPLAIN ALL PLANLogical plans at each stageTrack the evolution of logical plans
EXPLAIN MEMO PLANCost-based optimization process planAnalyze decisions made by the CBO optimizer
EXPLAIN SHAPE PLANPlan shapeQuickly 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:

  1. Basic query information: including ID, time, database, etc.
  2. SQL statement and execution plan.
  3. FE time consumption: including Plan Time, Schedule Time, etc.
  4. 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 NameMetric Meaning
BlocksProducedNumber of Data Blocks produced
CloseTimeTime spent by the operator in the close phase
ExecTimeTotal execution time of the operator across all phases
InitTimeTime spent by the operator in the Init phase
MemoryUsageMemory usage of the operator during the execution phase
OpenTimeTime spent by the operator in the Open phase
ProjectionTimeTime spent by the operator on projection
RowsProducedNumber of rows returned by the operator
WaitForDependencyTimeTime 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 AreaDescription
ExecuteTimeThe actual execution time of the PipelineTask, approximately equal to the sum of ExecTime of all operators in the task
WaitWorkerTimeThe time the task waits for an execution Worker, mainly determined by the cluster load
Time waiting on execution dependenciesThe 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 TypeValueDescription
Total execution time1.656 msApproximately equal to the sum of ExecTime of the two operators
Time waiting for Worker63.868 usThe current cluster load is not high, and a Worker executes the task immediately after it is ready
Time waiting on execution dependencies10.495 msEqual 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.