Your Redshift query takes 2 hours—how do you systematically debug & optimize it?

Debug it in a fixed order: confirm where the two hours go (waiting or running), read the plan and the optimizer's alerts, check the health of the tables involved, then fix data movement, table design or the SQL, and only after that add capacity.

1. Waiting or running?

SELECT query_id, status,
       queue_time / 60000000.0     AS queued_min,
       execution_time / 60000000.0 AS running_min,
       lock_wait_time / 60000000.0 AS lock_wait_min
FROM sys_query_history
WHERE query_id = 123456;

• Long queue time: the query waits for a slot. Look at workload management (queues, priorities, concurrency scaling) rather than the SQL.
• Long lock wait: another transaction holds a lock; find it in SVV_TRANSACTIONS.
• Long execution: continue below.

2. Read the plan and the alerts

• EXPLAIN the query. Red flags: DS_BCAST_INNER or DS_DIST_BOTH on large tables (rows shipped between nodes), nested loop joins, and steps with huge row counts.
• SYS_QUERY_DETAIL shows which steps took the time and how many rows each produced; STL_ALERT_EVENT_LOG gives the optimizer's diagnosis and fix (missing statistics, nested loop, large broadcast, ghost rows).

3. Check the tables

SELECT "table", diststyle, sortkey1, skew_rows, unsorted, stats_off, tbl_rows
FROM svv_table_info
WHERE "table" IN ('orders', 'order_lines', 'customers');

• stats_off high: run ANALYZE; the planner is guessing.
• unsorted high: run VACUUM SORT ONLY so sort-key filtering works again.
• skew_rows high: the distribution key concentrates rows on a few slices, so one slice does most of the work.

4. Fix the cause

• Data movement: distribute the two largest joined tables on the join key (DISTKEY), and use DISTSTYLE ALL for small dimensions, so joins happen locally. Or set AUTO and let automatic table optimisation choose.
• Scanning too much: sort key on the main filter column (often a date), and filter on it directly, without functions around it.
• The SQL: remove accidental cross joins and DISTINCT hiding duplicate rows from a bad join; select only needed columns; break a huge query into steps with temporary tables that are distributed for the next join.
• Repeated heavy logic: a materialized view with incremental refresh.

5. Then capacity

• Query priorities and query monitoring rules in WLM; concurrency scaling for peaks; resize, add RA3 nodes or raise Serverless base capacity only if the query is already efficient.

6. Prove it Run it again, compare elapsed time, rows scanned and data movement, and record what changed. Two-hour queries usually come down to one or two causes, most often a large broadcast or redistribution from poor distribution keys, or stale statistics.