Redshift query performance: queue time, data movement and workload management
Diagnose Redshift latency with queue and execution timing, distribution-plan evidence and workload context. Compare provisioned WLM and Serverless capacity without assuming every slow query needs more compute.
TL;DR: A Redshift query can spend most of its elapsed time waiting for execution. Rewriting the SQL may improve the execution stage while leaving the user's report almost as slow. Start by splitting time into queueing and execution, then inspect the stage that dominates the delay.
The SYS_QUERY_HISTORY reference exposes queue time, execution time and elapsed time in microseconds, along with status and result-cache information. Confirm visibility for the investigating identity: an empty result can reflect access scope, not an absence of queries.
Measure the slow part
This read-only query selects recent completed statements. It is intended for an authorized Redshift session; the timestamps and query identifiers come from that environment:
SELECT query_id,
status,
result_cache_hit,
elapsed_time / 1000000.0 AS elapsed_seconds,
queue_time / 1000000.0 AS queue_seconds,
execution_time / 1000000.0 AS execution_seconds
FROM sys_query_history
WHERE start_time >= DATEADD(hour, -1, GETDATE())
AND status = 'success'
ORDER BY elapsed_time DESC
LIMIT 20;
For an illustrative report, suppose elapsed time is 120 seconds, queue time is 90 seconds and execution is 25 seconds. The remaining five seconds represent time outside those two measured intervals; investigate the relevant lifecycle fields rather than assigning an unsupported cause. Cutting execution from 25 to five seconds saves 20 seconds if all other intervals remain unchanged. The report would still take 100 seconds.
That arithmetic changes the first investigation. Examine competing work and admission before spending the whole incident tuning a scan. Conversely, a query that starts immediately and executes for 120 seconds needs execution-plan and resource evidence.
| Observation | Useful next evidence | Premature conclusion |
|---|---|---|
| Large queue time | Concurrent workload, priorities and admission | The SQL is necessarily inefficient |
| Large execution time | Plan, scanned rows, skew and spills | More concurrency will fix it |
| Fast cached repetition | Result-cache flag and test conditions | The original execution became faster |
| Many slow dashboards at once | Shared dependencies and workload overlap | Every dashboard regressed independently |
Follow the data movement
A distributed join may need rows moved between compute slices. Redshift's data redistribution documentation describes plan labels such as DS_DIST_NONE, where join inputs are collocated, and DS_DIST_BOTH, where both inputs are redistributed. Movement can dominate even when the SQL text looks small.
Suppose a fact table has billions of rows and a join key is heavily concentrated in one value. Distributing on that key may concentrate work as well. A key that avoids movement for one join can still produce an uneven workload. Inspect row distribution and the important query mix before changing it.
Check whether the plan's row estimates resemble reality, whether filtering can reduce the scanned data, and whether intermediate work spills. Changes to sort or distribution design have write, maintenance and storage consequences. A smaller table copied to every node can help some joins, but copying a growing fact table is a different cost decision.
Match capacity advice to the deployment model
For a provisioned warehouse using automatic workload management, Redshift manages query concurrency and memory and supports workload priorities. Automatic WLM is different from manually assigning fixed slots. More admitted queries can compete for memory, so increasing concurrency does not guarantee shorter completion time.
Redshift Serverless exposes capacity in RPUs. Its capacity documentation describes base and maximum settings; those controls should not be explained as provisioned-cluster slot counts. Record whether the workload is provisioned or Serverless before proposing a control change.
A useful incident note names the query group or business workload, the delayed interval and the competing activity. For example, an illustrative hourly ingestion job overlapping executive reports suggests testing schedule or workload isolation. It does not yet justify a permanent capacity increase.
Compare like-for-like results
Keep data volume, parameters, concurrency and cache conditions visible in the before-and-after comparison. A result-cache hit is useful for the user but weak evidence of a changed execution plan. Retain both latency and resource-cost observations, particularly when a capacity change shifts spend to another billing dimension.
The slow-query concept covers general plan reasoning. Redshift adds distributed data placement and warehouse admission to that investigation.
Self-check: a new distribution design halves execution time, but an executive report misses its deadline. Revisit the complete elapsed-time breakdown and workload overlap. An execution improvement can be real while still being too small to meet the user's requirement.