How to Use EXPLAIN & EXPLAIN ANALYZE in Redshift

Why inspect a Redshift execution plan?

EXPLAIN shows the planned steps, node types, and data sizes before a query runs, so you can spot distribution or sort-key issues without spending credits.

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN is compile-time only.EXPLAIN ANALYZE executes the query and appends run-time metrics such as actual rows, memory, and execution time, making it indispensable for final tuning.

How do I generate a plan for a SELECT?

Prefix the statement with EXPLAIN. Redshift returns a JSON-like text plan in one column.Use the advisor view in Query Editor v2 for visuals.

EXPLAIN
SELECT c.name, SUM(oi.quantity * p.price) AS lifetime_value
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
JOIN OrderItems oi ON oi.order_id = o.id
JOIN Products p ON p.id = oi.product_id
GROUP BY c.name;

How do I collect run-time statistics?

Add ANALYZE to execute the query and measure each step.Wrap in a transaction to roll back if you do not want to commit data-changing statements.

EXPLAIN ANALYZE
SELECT *
FROM Orders
WHERE order_date > CURRENT_DATE - INTERVAL '30 days';

What plan columns matter most?

Node Type shows operations like DS_DIST_ALL_INNER or SORT. Rows and Width reveal estimated volume, while StartupCost and TotalCost indicate planner assumptions.In ANALYZE output, compare actual rows to estimates to detect skew.

How do I save or share a plan?

Copy the EXPLAIN text or query STL_EXPLAIN and SVL_QUERY_REPORT tables by filtering with the query id. Store them in Galaxy Collections to collaborate.

Best practices for Redshift plans

1) Always examine distribution-style nodes; data movement kills speed. 2) Aim for even row counts across slices.3) Run ANALYZE on tables so estimates stay accurate.

How can I reduce DS_DIST_ALL_INNER?

Make the join key of both tables match distribution keys or use KEY distribution on large tables joined together. Consider copying small dimension tables to ALL.

When should I avoid EXPLAIN ANALYZE?

Skip it on terabyte-scale DELETEs unless you test in a dev cluster; ANALYZE will run the command in full.

.