How I Stopped Recalculating the Same MAX(date) Subquery on Redshift

A subquery that looked perfectly fine on its own was quietly running dozens of times a day across our Redshift pipeline. Here's the story of how I found it, why it hurt specifically on Redshift, and the fix that made it a non-issue.

The setup

I had a query that needed the most recent record for a given entity — a fairly common pattern:

SELECT *
FROM events
WHERE event_date = (
  SELECT MAX(event_date)
  FROM events e2
  WHERE e2.entity_id = events.entity_id
);

This is a correlated subquery — the inner SELECT references the outer query's row (e2.entity_id = events.entity_id), so it can't just run once. It worked, it was correct, and it was fast enough on its own. I moved on.

Where it went wrong

The problem wasn't this one query — it was that the same "latest record per entity" logic kept showing up downstream: a dashboard filter, a data quality check, a reconciliation job, a couple of reports. Each of those had its own copy of a similar MAX(date) subquery.

Individually, every query looked fine in isolation. Nobody flagged it in review because each query's runtime on its own was acceptable. The real cost only showed up in aggregate — the same underlying calculation was being recomputed from scratch every time something needed it, hitting the same table repeatedly.

Why this hurts more on Redshift specifically

This is where Redshift's architecture makes the problem worse than it might be elsewhere. Redshift is a columnar, MPP (massively parallel processing) engine, and correlated subqueries interact badly with that design for a few specific reasons:

• Limited parallelism. Because a correlated subquery is logically re-evaluated per outer row, it constrains how much the query can be parallelized across compute nodes — you lose some of the benefit of Redshift's distributed architecture.
• Sub-optimal query plans. Redshift's optimizer has documented issues generating efficient plans for correlated subqueries, particularly with EXISTS / NOT EXISTS patterns — sometimes producing nested loop joins, which are the slowest join type Redshift supports.
• Data movement across nodes. If the subquery's join key isn't the table's distribution key, Redshift has to redistribute rows across the cluster to evaluate the correlation, adding network I/O on top of the recomputation cost.

AWS's own query design guidance is explicit about this: use a CASE expression for complex aggregations instead of scanning the same table multiple times, and prefer subqueries only when they return a small result set (under roughly 200 rows) used purely as a filter — not as a repeated per-row calculation.

The fix

Instead of repeating the subquery everywhere it was needed, I computed the max date once and joined it back to the base table as an extra column:

SELECT
  t1.*,
  t2.max_date
FROM events t1
JOIN (
  SELECT entity_id, MAX(event_date) AS max_date
  FROM events
  GROUP BY entity_id
) t2 ON t1.entity_id = t2.entity_id;

Two things mattered for making this actually fast on Redshift, not just "correct":

1. The join key matches the table's distribution key. When you join on the distribution key, Redshift can complete the join on each node in parallel without shuffling rows across the cluster. If entity_id isn't your distribution key, this join can still trigger a redistribution step — worth checking with EXPLAIN before assuming it's free.
2. The result gets reused, not recalculated. Now max_date is a column that lives on the row. Any downstream query, dashboard filter, or reconciliation check just reads that column and compares (event_date = max_date) instead of running its own version of the subquery.

An alternative: window functions

A window function version is also worth considering on Redshift, especially if you want to flag which row is the latest, not just know the date:

SELECT *
FROM (
  SELECT
    *,
    MAX(event_date) OVER (PARTITION BY entity_id) AS max_date,
    ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY event_date DESC) AS rn
  FROM events
) ranked
WHERE rn = 1;

This avoids a self-join entirely and often performs better than either the correlated subquery or the join approach on large tables, since Redshift can compute it as a single sort-and-scan rather than a join. Redshift performance guides specifically recommend window functions over self-joins as a general optimization pattern.

Materializing beyond a single query

If this value needs to be read very frequently across many downstream consumers, you have a few options on Redshift specifically:

• A CTE if it's scoped to a single pipeline run — just be aware that Redshift sometimes rewrites WITH clauses into temporary volt_tt tables internally, which can add overhead on complex CTEs. Simpler joins or window functions sometimes outperform an equivalent CTE for this reason.
• A materialized view if the underlying data doesn't change constantly — Redshift materialized views precompute the result set, which is ideal for a value like "max date per entity" that many queries read but few queries update. Note that Redshift disables automatic materialized view refresh by default (citing planning-time overhead), so you'd trigger a manual refresh as the last step of your ETL job.
• A physical column updated via your ETL job if this needs extremely frequent reads and refresh timing is predictable — this trades some storage and write complexity for guaranteed fast reads.

Takeaway

• Correlated subqueries repeated across multiple queries multiply their cost silently — nothing looks wrong until you count the total load, and Redshift's MPP architecture makes that cost worse due to limited parallelism and potential data redistribution
• Precomputing shared logic once — via a join on the distribution key, a window function, or a materialized view — turns N recalculations into one
• Always check the query plan with EXPLAIN when in doubt. Don't assume a subquery, join, or window function is faster without testing against your actual table's distribution and sort keys

Have you run into logic that looked fine solo but got expensive once it was reused across a Redshift pipeline? Curious how others have handled this.