What is the Redshift SQL order of execution (especially with subqueries)?

Question

How does Redshift evaluate an INNER JOIN that’s outside a derived table in the FROM clause? Does it filter before the derived table “selects” its columns?

I’m trying to understand the expected (and/or documented) Redshift behavior for a query shaped like this:

```
WITH audit AS (
    SELECT
        id,
        hash_value,
        commit_ts
    FROM audit_table
    WHERE commit_ts >= '2025-01-01'
),
current AS (
    SELECT
        c.id,
        c.hash_value,
        c.load_ts
    FROM (
        SELECT
            id,
            hash_value,
            load_ts
        FROM current_table
    ) c
    INNER JOIN audit a
        ON a.id = c.id
),
joined AS (
    SELECT
        a.id,
        a.hash_value AS audit_hash,
        c.hash_value AS current_hash,
        a.commit_ts,
        c.load_ts
    FROM audit a
    LEFT JOIN current c
        ON a.id = c.id
    WHERE
        (a.hash_value <> c.hash_value OR c.id IS NULL)
)
SELECT
    id,
    audit_hash,
    current_hash,
    commit_ts,
    load_ts
FROM joined;
```

Conceptually, I’m unclear on whether Redshift treats the derived table:

```
(SELECT id, hash_value, load_ts FROM current_table) c
```

as something that is fully produced first (i.e., selects/project those columns for all rows), and then the outer INNER JOIN filters it, or whether Redshift’s planner effectively merges this into one relational operation such that the join constraint can be applied “as early as possible,” meaning the join filters rows before (or during) projecting id, hash_value, load_ts.

So the core questions:
1. In Redshift, does an INNER JOIN outside a derived table (subquery in FROM) act as a logical filter before the derived table’s select-list is materialized, or is the derived table treated as a separate, fully evaluated step?
2. Does Redshift treat derived tables like this as optimization barriers, or does it inline them and reorder/push down operations?
3. If current_table is Iceberg/S3-backed, does Redshift have any ability to reduce scan/I/O based on the join key (e.g., runtime filters), or should I expect a full scan regardless?

I’m not asking about SQL semantics in general; I’m specifically trying to understand what Redshift is supposed to do with this structure.

re:Post Agent · Answer

In Amazon Redshift, the query execution order follows a specific sequence, particularly when dealing with subqueries and complex operations.

For your specific query structure with the derived table in the FROM clause followed by an INNER JOIN, Redshift's query optimizer is designed to be intelligent about execution. Redshift doesn't necessarily materialize the entire derived table before applying the join filter. Instead, the query optimizer can often merge these operations and apply filters as early as possible in the execution plan.

The query optimizer in Redshift analyzes the entire query and creates an execution plan that attempts to minimize data movement and processing. For your specific questions:

1. Redshift's optimizer can push predicates (filters) from the outer query into derived tables and subqueries when possible. This means the INNER JOIN condition can effectively filter rows before or during the materialization of the derived table's select list, rather than materializing the entire derived table first.

2. Derived tables are not strict optimization barriers in Redshift. The optimizer can often inline them and reorder operations to improve performance, especially when the derived table is a simple projection like in your example.

3. For Redshift Spectrum tables (like Iceberg/S3-backed tables), Redshift can use predicate pushdown to reduce the amount of data scanned from S3. However, the effectiveness depends on several factors:
- The partitioning of your external table
- Whether the join columns are used in partitioning
- The complexity of the query

When processing your WITH clause CTEs (Common Table Expressions), Redshift treats them as temporary tables for the duration of the query. The query optimizer can sometimes treat these as common subexpressions that are evaluated once and reused if referenced multiple times.

For hierarchical queries (though not directly applicable to your example), Redshift processes them in a specific order: FROM clause with joins first, then CONNECT BY clause, and finally the WHERE clause.

To truly understand how Redshift is executing your specific query, you should use the EXPLAIN command to review the actual query plan. This will show you the execution steps, including how joins are processed and whether predicate pushdown is occurring.  
**Sources**  
[CONNECT BY clause - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/r_CONNECT_BY_clause.html)  
[Reviewing query plan steps - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/reviewing-query-plan-steps.html)  
[WITH clause - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html)

What is the Redshift SQL order of execution (especially with subqueries)?

Add your answer

Relevant content