Case Study 1: Forty Minutes to Four Seconds

"Nobody had written a slow query. Somebody had written a fast query eleven times."

Executive Summary

Kestrel's customer-cohort model took 41 minutes and was the longest step in the nightly DAG, consuming a third of the window before the 6am SLA.

It contained no obviously bad SQL. Every individual construct was reasonable. The model was six correlated subqueries in the SELECT list and five window functions with four different PARTITION BY clauses, and the combination meant the engine re-scanned the fact table six times and re-sorted it four.

The rewrite took a morning and produced the same output in 4.1 seconds — a 600× improvement, from nothing but set-based restructuring.

This case study is §18.1, §18.2, and §18.10 applied together. It is here because the original was not written badly; it accumulated, one reasonable addition at a time, and every addition was individually defensible.

Skills applied: set-based thinking (§18.1); window functions replacing correlated subqueries (§18.2); consolidating partitionings (§18.10); reading a transformation plan (§18.10).

Background

The model. gold.dim_customer_cohort — one row per customer, with cohort membership, lifetime value, order counts, recency, and a set of derived flags used by the growth team.

How it grew. Written in 2024 with three columns. Over eighteen months it gained fourteen more, one at a time, each added by whoever needed it, each reviewed and merged.

The shape it had reached:

SELECT
    c.customer_id,
    c.region,
    -- 1
    (SELECT MIN(order_date) FROM gold.fct_order o
      WHERE o.customer_id = c.customer_id)                AS first_order_date,
    -- 2
    (SELECT MAX(order_date) FROM gold.fct_order o
      WHERE o.customer_id = c.customer_id)                AS last_order_date,
    -- 3
    (SELECT COUNT(*) FROM gold.fct_order o
      WHERE o.customer_id = c.customer_id)                AS order_count,
    -- 4
    (SELECT SUM(net_revenue_cents) FROM gold.fct_order o
      WHERE o.customer_id = c.customer_id)                AS lifetime_cents,
    -- 5
    (SELECT SUM(net_revenue_cents) FROM gold.fct_order o
      WHERE o.customer_id = c.customer_id
        AND o.order_date >= CURRENT_DATE - 90)            AS cents_90d,
    -- 6
    (SELECT COUNT(*) FROM gold.fct_return r
      JOIN gold.fct_order o USING (order_id)
     WHERE o.customer_id = c.customer_id)                 AS return_count
  FROM gold.dim_customer c
 WHERE c.is_current;

Plus a second stage with five window functions over the result, partitioned variously by region, by cohort month, by segment, and by nothing.

Every subquery is correct. Every one was added by someone who needed exactly that number and reasonably followed the pattern already there.

The Problem

41 minutes, and no single part of it looked like the cause.

The plan showed the fact table scanned six times:

Nested Loop  (actual time=... rows=1904221 loops=1)
  ->  Seq Scan on dim_customer c  (rows=1904221)
  ->  SubPlan 1
        ->  Aggregate  (actual time=0.31..0.31 rows=1 loops=1904221)   ← ×1.9M
              ->  Index Scan on fct_order  (rows=3)
  ->  SubPlan 2
        ->  Aggregate  (loops=1904221)                                  ← ×1.9M
  ...  SubPlan 3, 4, 5, 6

loops=1904221 is the number to read. Each correlated subquery is evaluated once per customer row — 1.9 million times, six times over. At 0.31 ms each:

$$1{,}904{,}221 \times 6 \times 0.00031 \text{ s} = 3{,}542 \text{ s} = 59 \text{ minutes of subplan time}$$

Partly parallelized down to the observed 41 minutes.

🔎 Read the Plan — loops= is the number that names this problem

Most plan-reading advice is about node types and costs. The single most diagnostic field for a slow transformation is loops=, and it appears in every plan and is routinely skipped.

text -> Aggregate (actual time=0.31..0.31 rows=1 loops=1904221) ^^^^^^^^^^^

actual time is per loop, not total. A node showing 0.31 ms looks trivial; multiplied by 1.9 million loops it is ten minutes. The plan is telling you plainly and the units are easy to misread.

The check: scan the plan for the largest loops= value. Anything in the millions is a correlated subquery or a nested loop over a large outer side, and it is almost always the whole problem.

And the arithmetic to do in your head: actual time × loops is the node's real contribution. That is the number to rank nodes by, and no tool computes it for you.

The Analysis

Two problems, and they compound.

Problem 1: six scans instead of one. Each correlated subquery independently visits fct_order for each customer. They are all asking about the same rows.

Problem 2: four sorts instead of one. The second stage's window functions:

ROW_NUMBER() OVER (PARTITION BY region        ORDER BY lifetime_cents DESC),
NTILE(10)    OVER (PARTITION BY region        ORDER BY lifetime_cents DESC),
RANK()       OVER (PARTITION BY cohort_month  ORDER BY lifetime_cents DESC),
AVG(lifetime_cents) OVER (PARTITION BY segment),
SUM(lifetime_cents) OVER ()

Four distinct window specifications means four sorts of 1.9 million rows. The first two share a specification and are computed in one pass; the rest each force a re-sort.

The plan showed it, and nobody had looked:

WindowAgg
  ->  Sort  (Sort Key: region, lifetime_cents DESC)   Sort Method: external merge  Disk: 284MB
      ->  WindowAgg
            ->  Sort  (Sort Key: cohort_month, ...)   Disk: 284MB
                ->  WindowAgg
                      ->  Sort  (Sort Key: segment)   Disk: 284MB
                          ->  WindowAgg

Three external merge sorts spilling 284 MB each — Chapter 8 §8.8's spill signal, in a transformation.

The Decision

The rewrite, in two moves.

Move 1: one aggregate instead of six correlated subqueries.

WITH order_agg AS (
    -- ONE pass over fct_order. Everything the six subqueries wanted.
    SELECT customer_id,
           MIN(order_date)                            AS first_order_date,
           MAX(order_date)                            AS last_order_date,
           COUNT(*)                                   AS order_count,
           SUM(net_revenue_cents)                     AS lifetime_cents,
           -- The 90-day figure becomes a FILTERed aggregate rather than a
           -- second scan. Portable alternative:
           --   SUM(CASE WHEN order_date >= ... THEN net_revenue_cents ELSE 0 END)
           SUM(net_revenue_cents) FILTER (
               WHERE order_date >= CURRENT_DATE - 90)  AS cents_90d
      FROM gold.fct_order
     GROUP BY customer_id
),
return_agg AS (
    -- The returns count needs its own grain, so it gets its own aggregate --
    -- and joining two pre-aggregated sets is NOT the fan-out trap, because
    -- both are already one row per customer. Ch. 6 §6.9.
    SELECT o.customer_id, COUNT(*) AS return_count
      FROM gold.fct_return r
      JOIN gold.fct_order o USING (order_id)
     GROUP BY 1
)
SELECT c.customer_id, c.region, c.segment,
       a.first_order_date, a.last_order_date, a.order_count,
       a.lifetime_cents, a.cents_90d,
       COALESCE(r.return_count, 0) AS return_count
  FROM gold.dim_customer c
  LEFT JOIN order_agg  a USING (customer_id)
  LEFT JOIN return_agg r USING (customer_id)
 WHERE c.is_current

Six scans become two, and FILTER (or a CASE inside the aggregate) removes the seventh.

Move 2: consolidate the window partitionings.

The four specifications were examined and two were unnecessary:

  • RANK() OVER (PARTITION BY cohort_month ...) — used by one dashboard, which had been decommissioned eight months earlier. Removed after checking the query log (Chapter 8's Case Study 1's method).
  • AVG(...) OVER (PARTITION BY segment) — kept, and moved into the order_agg CTE's grain where it is a cheap GROUP BY segment joined back, rather than a window over 1.9 million rows.

Two specifications remain, and the two that share one compute in a single pass:

SELECT *,
       ROW_NUMBER() OVER w AS region_rank,
       NTILE(10)    OVER w AS region_decile,
       SUM(lifetime_cents) OVER () AS total_cents
  FROM base
WINDOW w AS (PARTITION BY region ORDER BY lifetime_cents DESC);

The WINDOW clause names a specification once and reuses it. It is standard SQL, widely supported, and almost nobody uses it — and it makes the sharing explicit to the reader as well as to the engine.

📐 Design Decision — Rewrite, or add an index?

The first proposal was an index on fct_order (customer_id, order_date), which would have made each of the 11.4 million subquery executions faster.

It would have worked, to a point: the team measured it at 19 minutes, down from 41.

They rewrote instead, and the reasoning is worth following:

  • An index makes the wrong shape faster. 11.4 million index lookups is still 11.4 million lookups; the rewrite makes it two scans.
  • The index has an ongoing cost on every write to fct_order — 6.48 million rows a year (Chapter 7 §7.2).
  • The rewrite is more readable. The CTE version names its steps; the six-subquery version does not.
  • The index would have hidden the problem at 19 minutes, which is inside the window, so nobody would have looked again until the table grew.

The general rule: an index that makes a badly-shaped query tolerable postpones the rewrite and removes the pressure to do it. Reach for the index when the shape is already right.

What rewriting cost: a morning, and a re-verification that the output was identical — which is the part that actually took the time, and which §"What happened" covers.

What Happened

Before After
Runtime 41 min 4.1 s
Scans of fct_order 6 2
Sorts of 1.9M rows 4 1
External merge spill 852 MB 0
Lines of SQL 94 71

600×, and the model is shorter.

The verification took longer than the rewrite, and it is the part worth copying:

-- Full-row comparison, both directions. Not a row count -- a row count
-- passes when two rows have swapped values.
SELECT 'in_old_not_new' AS side, * FROM (
    SELECT * FROM old_model EXCEPT SELECT * FROM new_model)
UNION ALL
SELECT 'in_new_not_old', * FROM (
    SELECT * FROM new_model EXCEPT SELECT * FROM old_model);
-- expect zero rows

It returned 41 rows on the first attempt. All 41 were customers with no orders, where the old model's correlated COUNT(*) returned 0 and the new model's LEFT JOIN returned NULL.

The old behavior was arguably wrong — a customer with no orders has an order count of zero, not unknown — and it had been correct for eighteen months by accident of the correlated-subquery form. The COALESCE in the rewrite was added deliberately, and the discrepancy was recorded rather than silently fixed, because a downstream model had been relying on the null.

Three follow-on effects:

The nightly DAG's critical path moved. With 41 minutes removed, a different model became the longest step — which is the normal outcome of any optimization and is worth expecting rather than being surprised by.

A codebase audit found correlated subqueries in eleven other models. Four were on small tables and harmless. Seven were rewritten, saving a further 26 minutes across the DAG.

The WINDOW clause spread. Nobody on the team had used it; after this it appears in nine models, and the team's view is that its main value is making shared partitioning visible to a reviewer, not the performance.

Lessons

  1. loops= is the most diagnostic field in a slow transformation's plan, and actual time is per loop. Multiply them; no tool does it for you.

  2. Six correlated subqueries asking about the same rows are six scans. One GROUP BY in a CTE replaces them.

  3. FILTER (or CASE inside an aggregate) replaces a second scan for a filtered variant of the same measure.

  4. Each distinct window specification is a sort. Four specifications over 1.9 million rows is four sorts and, here, 852 MB of spill.

  5. The WINDOW clause names a specification once, is standard, is widely supported, and is almost never used. Its main value is making the sharing visible to a reviewer.

  6. An index that makes a badly-shaped query tolerable postpones the rewrite and removes the pressure to do it. 19 minutes is inside the window and nobody looks again.

  7. Verify with a full-row EXCEPT in both directions, not a row count. A row count passes when two rows have swapped values.

  8. Expect the verification to find real discrepancies, and record rather than silently fix them — 41 rows differed, and the old behavior had been accidentally wrong for eighteen months while something downstream relied on it.

  9. The model was not written badly. It accumulated, one defensible addition at a time. Audit for the shape, which found seven more.

Questions for Discussion

  1. Each of the six subqueries was added and reviewed separately, and each was reasonable. What review practice catches an accumulating shape that no single change introduces?

  2. The team rejected the index because it would have hidden the problem at 19 minutes. Is "it would work but obscure the real issue" a legitimate reason to reject a fix? When is it not?

  3. The verification found 41 rows where the old model returned 0 and the new returned NULL, and something downstream relied on the null. How would you decide which behavior to keep?

  4. Removing 41 minutes moved the DAG's critical path to a different model. How should a team plan optimization work given that the bottleneck moves?

  5. The RANK() OVER (PARTITION BY cohort_month ...) column served a dashboard decommissioned eight months earlier. How many columns in your own models are in that position, and how would you find out?

  6. The audit found correlated subqueries in eleven models, seven worth rewriting. Design the lint rule. What is its false-positive rate on small tables?

  7. This chapter's material is the oldest in the book and this case study is a 600× win from applying it. Why do you think set-based restructuring is so often left undone?