Query Analysis
The Query Analysis dashboard exposes Redshift query performance data across three views. Use it to identify the most expensive queries by runtime and disk spill, and to diagnose WLM queue contention that increases wait times.
View 1: Top Queries by Runtime
Queries are aggregated by MD5 fingerprint so that identical query patterns from different users or times are grouped together. This reveals which query shapes are responsible for the most cumulative execution time.
| Column | Description |
|---|---|
| Query Fingerprint | MD5 hash of the normalized query text (with literals replaced). Click to expand full SQL. |
| Execution Count | How many times this query pattern ran in the selected time window |
| Avg Runtime (s) | Average execution duration per run |
| Max Runtime (s) | Worst-case execution duration — helps identify outlier runs |
| Compile Time (s) | Average query compilation overhead. High compile times suggest complex or rarely-executed queries. |
| Application | Application name from sys_connection_log. Filterable. |
Data source: stl_query joined with stl_query_metrics, 7-day rolling window.
View 2: Top Queries by Disk Spill
Disk spill occurs when a query's memory allocation is exhausted and intermediate results are written to disk. This dramatically increases execution time and can degrade cluster performance for other queries.
This view shows the same query aggregation as View 1 but ordered by total disk spill volume:
- Spill GB: Total disk spill across all runs of this query pattern. Formula:
spilled_block_count × 8 KB ÷ (1024²) GB - Avg Spill per Run (GB): Per-execution spill average
Data source: sys_query_detail (spill_to_disk column), 7-day window.
Reducing Disk Spill
View 3: WLM Queue Analysis
The WLM Queue Analysis view shows queries that spent time waiting in WLM queues before execution began. High queue times indicate queue slot contention — more queries arriving than slots available.
| Column | Description |
|---|---|
| Query ID | Redshift query ID. Click to see full SQL text and execution plan summary. |
| WLM Queue | Queue name the query was routed to |
| Queue Time (s) | Time spent waiting for a WLM slot. Non-zero values indicate contention. |
| Exec Time (s) | Time spent actually executing after a slot was granted |
| Queue:Exec Ratio | Queue time divided by exec time. Ratios > 0.5 indicate significant WLM overhead. |
Data source: sys_query_history filtered toqueue_time > 0, 7-day window.
Query Detail Panel
Clicking any query row across all three views opens the Query Detail Panel:
- Full SQL Text — Original query with syntax highlighting
- Execution Plan Summary — Step-by-step plan costs from
stl_explain - Step-Level Timing — Which plan step consumed the most time
- Alert Events — Any
stl_alert_event_logentries associated with this query