The short answer
Prefixing any SQL statement with EXPLAIN returns the execution plan generated by the Redshift optimizer. Reading this plan allows you to spot expensive physical operations and network movement bottlenecks before running a long analytical query.
-> XN Hash Join DS_BCAST_INNER (cost=25.00..1500000.00 rows=50000000)
Hash Cond: ("outer".customer_id = "inner".customer_id)
-> XN Seq Scan on sales "outer" (cost=0.00..50000.00 rows=50000000)
-> XN Hash (cost=12.50..12.50 rows=1000)
-> XN Seq Scan on customers "inner" (cost=0.00..12.50 rows=1000)Key indicators to inspect
- Data redistribution indicators: Look closely at the join annotations: DS_DIST_NONE means a colocated join where zero data travels across the network. DS_DIST_ALL_NONE means the inner table is distributed with DISTSTYLE ALL across all compute nodes. DS_BCAST_INNER means the inner table is broadcast to every slice. DS_DIST_BOTH is the most expensive case where both tables are redistributed simultaneously across slices.
- Cost estimates: Displayed as
cost=start_cost..total_cost. These are relative units representing disk I/O and CPU work. Pay attention to execution steps with disproportionately large total cost jumps. - Sequential Scans: An
XN Seq Scanindicates that Redshift is scanning table blocks sequentially from disk. Check if a common filter column is missing from the table sort key, preventing zone map block skipping. - STL_ALERT_EVENT_LOG: After running a slow query, query the system table
STL_ALERT_EVENT_LOG. It logs actionable warnings such as missing table statistics, excessive nested loops, or hash join memory spills to disk.