BrevFeed
Sign in

54. Capstone: tune a slow query

📖 Reading · 6 min
💡 Every code box below is live — edit it and hit Run.

The brief

A dashboard shows, for every customer, their total lifetime spend, their most-rented film category, and whether they've rented anything in the last 30 days ("active" vs. "lapsed"). The engineer who wrote it used the most natural-looking approach — three correlated subqueries per customer row, the "once per outer row" cost Module 6's correlated-subquery lesson warned about. Your job: diagnose it the same way, and fix it.

The slow query

SELECT
    c.customer_id, c.first_name, c.last_name,
    (SELECT sum(amount) FROM payment p WHERE p.customer_id = c.customer_id) AS lifetime_spend,
    (
        SELECT cat.name
        FROM rental r
        JOIN inventory i ON i.inventory_id = r.inventory_id
        JOIN film f ON f.film_id = i.film_id
        JOIN film_category fc ON fc.film_id = f.film_id
        JOIN category cat ON cat.category_id = fc.category_id
        WHERE r.customer_id = c.customer_id
        GROUP BY cat.name
        ORDER BY count(*) DESC, cat.name
        LIMIT 1
    ) AS favorite_category,
    EXISTS (
        SELECT 1 FROM rental r
        WHERE r.customer_id = c.customer_id
        AND r.rental_date > current_date - 30
    ) AS is_active
FROM customer c;

Your task

  1. Measure first (Module 10's EXPLAIN ANALYZE lesson) — run this with EXPLAIN ANALYZE and note the total execution time and the loops= counts on the subplans. Don't guess; measure.
  2. Diagnose — which of the three subqueries is contributing the most cost? Are any of them cheaper than they look (hint: think about what EXISTS, from Module 6, actually costs versus the other two)?
  3. Rewrite each piece using the set-based techniques this course built: GROUP BY for the aggregate, ROW_NUMBER() OVER (PARTITION BY ...) for favorite category (Module 7), and consider whether the EXISTS really needs rewriting at all.
  4. Measure again — confirm the rewrite is both correct (identical results) and faster.

Reference solution

WITH customer_spend AS (
    SELECT customer_id, sum(amount) AS lifetime_spend
    FROM payment
    GROUP BY customer_id
),
category_counts AS (
    SELECT
        r.customer_id, cat.name AS category,
        count(*) AS rental_count,
        row_number() OVER (PARTITION BY r.customer_id ORDER BY count(*) DESC, cat.name) AS rn
    FROM rental r
    JOIN inventory i ON i.inventory_id = r.inventory_id
    JOIN film f ON f.film_id = i.film_id
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category cat ON cat.category_id = fc.category_id
    GROUP BY r.customer_id, cat.name
)
SELECT
    c.customer_id, c.first_name, c.last_name,
    cs.lifetime_spend,
    cc.category AS favorite_category,
    EXISTS (
        SELECT 1 FROM rental r
        WHERE r.customer_id = c.customer_id
        AND r.rental_date > current_date - 30
    ) AS is_active
FROM customer c
LEFT JOIN customer_spend cs ON cs.customer_id = c.customer_id
LEFT JOIN category_counts cc ON cc.customer_id = c.customer_id AND cc.rn = 1;

The EXISTS subquery was deliberately left alone. This is the diagnostic judgment call Module 10's lessons kept building toward: EXISTS short-circuits on the first matching row and, with an index on (customer_id, rental_date), is already cheap per customer — rewriting it into a third CTE would add complexity without a measurable benefit. Not everything that "runs once per row" is automatically worth rewriting; the discipline is measuring which subquery actually dominates the total cost, not reflexively rewriting all three because one pattern looked suspicious.

A real correctness bug, caught by the verification step itself

The first version of this reference solution did not have , cat.name in either ORDER BY count(*) DESC above — and Step 4's correctness check (comparing every customer's result between both versions) caught 105 mismatched customers as a result. The cause: 172 customers have a genuine tie for their top rental category (two or more categories with the identical rental count), and with no tiebreaker, ORDER BY count(*) DESC / ORDER BY ... count(*) DESC inside a window function are each free to break that tie in whatever order is physically convenient — which is not guaranteed to agree between a correlated subquery's execution path and a window function's, even though both are "the same tie" logically. This is the ordering non-determinism of ties and Module 7's ROW_NUMBER tiebreak note, showing up for real, in a rewrite that looked obviously correct on inspection. Adding cat.name as an explicit secondary sort key in both versions (shown in the queries above) fixed it — after which the correctness check reports 0 mismatches, for real, against all 599 customers.

This is worth sitting with as the actual point of this capstone: a rewrite that produces the right answer for most rows and looks correct by eye is still a bug, and the only way this one was caught was by actually running an automated, row-by-row comparison — not by reading the SQL and reasoning "that looks right," which is exactly what missed it the first time.

Check yourself

  1. What's the diagnostic first step before rewriting ANY suspected-slow query, and why does skipping it risk optimizing the wrong thing?
  2. Why was the EXISTS subquery left unchanged in the reference solution, when the other two were rewritten?
  3. How would you verify the rewritten query is actually correct, not just faster?
← Previous Next →