How to Analyze Execution Plans in Redshift

How do I analyze and optimize execution plans in Amazon Redshift?

EXPLAIN in Redshift shows the planned steps, node types, and data flow so you can spot bottlenecks and tune queries.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why run EXPLAIN before tuning?

EXPLAIN reveals how Redshift intends to execute your query—scan type, join order, distribution, and estimated rows. Viewing the plan first prevents blind index changes and pinpoints the exact step that needs work.

How do I generate a Redshift execution plan?

Prefix your query with the keyword EXPLAIN. The command returns a JSON-like plan in a single column called QUERY PLAN.

Example

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 read the output?

Read from the bottom up. The last line is the plan root (often XN GroupAggregate). Indented lines above represent child nodes such as scans, joins, or aggregates. Key columns include Rows (estimate) and Bytes.

Common node types

DS_DIST_BCAST_INNER: Broadcast inner table to all slices.
DS_DIST_HASH: Re‐hash and redistribute rows.
DS_SORTED_MERGE: Merge join on sorted streams.
SCAN: Sequential or index scan of a table.

What performance issues appear in plans?

Look for DS_DIST redistribution nodes—they signal large network shuffles. Also watch for sequential scans on massive tables, high estimated rows, and nested loop joins on unsorted data.

How can I fix costly plan steps?

Match DISTSTYLE and DISTKEY across joined tables to remove redistribute nodes. Add SORTKEYs to support merge joins. Break huge aggregations into CTES. Filter early with WHERE to shrink intermediate sets.

When should I use EXPLAIN VERBOSE?

Use EXPLAIN VERBOSE to include predicate text and distribution keys. This deeper detail helps verify that Redshift uses the intended DISTKEY/SORTKEY.

Should I trust row estimates?

Estimates rely on block-level statistics. After large INSERTs, run ANALYZE to refresh stats before depending on EXPLAIN numbers.

Best practice checklist

1) Run ANALYZE first. 2) EXPLAIN every expensive query. 3) Eliminate DS_DIST nodes. 4) Favor merge/hash joins over nested loops. 5) Re-EXPLAIN after schema changes.

Why How to Analyze Execution Plans in Redshift is important

How to Analyze Execution Plans in Redshift Example Usage


-- Find top 5 customers by lifetime value and inspect plan
EXPLAIN
SELECT c.id, 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.id, c.name
ORDER BY lifetime_value DESC
LIMIT 5;

How to Analyze Execution Plans in Redshift Syntax


EXPLAIN [VERBOSE] query;

-- Example with options
EXPLAIN VERBOSE
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
WHERE o.order_date >= '2024-01-01'
GROUP BY c.name;

Common Mistakes

Ignoring distribution skew: Users focus on scan types but miss that <code>DS_DIST</code> nodes redistribute terabytes. Fix by aligning DISTKEYs on large joins.
Trusting stale statistics: Running EXPLAIN after bulk loads without <code>ANALYZE</code> gives bad row estimates, leading to the wrong tuning decisions. Always refresh stats first.