Chapter 14: Diagnosing and Fixing a Slow Query, End to End

Why this matters

"Diagnose why this query is slow" is one of the most common practical database interview exercises, and one of the most common real production tasks — this chapter walks through a complete, realistic diagnostic process end to end, tying together EXPLAIN (Chapter 5), indexing (Chapter 6), and the N+1 pattern (Chapter 5) into one coherent workflow, the same way the OS and Networking books' closing chapters tied their material into complete walkthroughs.

The scenario

A reporting endpoint that shows "a customer's total spending this year" has become unacceptably slow as the orders table has grown to several million rows:

SELECT customers.name, SUM(orders.total_cents) AS total_spent
FROM customers
JOIN orders ON orders.customer_id = customers.id
WHERE orders.created_at >= '2026-01-01'
  AND customers.id = 42
GROUP BY customers.name;

Step 1: Measure, don't guess — run EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT customers.name, SUM(orders.total_cents) AS total_spent
FROM customers
JOIN orders ON orders.customer_id = customers.id
WHERE orders.created_at >= '2026-01-01'
  AND customers.id = 42
GROUP BY customers.name;

HashAggregate  (cost=45231.50..45231.51 rows=1 width=40) (actual time=890.2..890.2 rows=1 loops=1)
  Group Key: customers.name
  ->  Hash Join  (cost=12.50..45180.00 rows=8200 width=36) (actual time=0.5..875.1 rows=340 loops=1)
        Hash Cond: (orders.customer_id = customers.id)
        ->  Seq Scan on orders  (cost=0.00..44900.00 rows=180000 width=12) (actual time=0.1..820.3 rows=340 loops=1)
              Filter: (created_at >= '2026-01-01' AND customer_id = 42)
              Rows Removed by Filter: 2999660
        ->  Hash  (cost=10.00..10.00 rows=200 width=36)
              ->  Seq Scan on customers ...
Planning Time: 0.4 ms
Execution Time: 891.0 ms

Reading this precisely: the Seq Scan on orders with Rows Removed by Filter: 2999660 is the smoking gun — nearly 3 million rows scanned and discarded to find 340 matching ones, exactly the diagnostic signature Chapter 5 introduced. The 891ms execution time is almost entirely attributable to this one sequential scan.

Step 2: Identify the right index

[IN PRACTICE] The query filters on orders.customer_id (exact match) and orders.created_at (a range) together. Per Chapter 6's composite index guidance, put the exact-match column first, the range column second:

CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders(customer_id, created_at);
-- CONCURRENTLY, per Chapter 13, since this is a large, live, actively-written production table.

Step 3: Verify the fix with EXPLAIN ANALYZE again

HashAggregate  (cost=245.31..245.32 rows=1 width=40) (actual time=0.8..0.8 rows=1 loops=1)
  Group Key: customers.name
  ->  Nested Loop  (cost=0.85..244.50 rows=340 width=36) (actual time=0.05..0.7 rows=340 loops=1)
        ->  Index Scan using idx_orders_customer_created on orders
              (cost=0.42..180.20 rows=340 width=12) (actual time=0.03..0.4 rows=340 loops=1)
              Index Cond: (customer_id = 42 AND created_at >= '2026-01-01')
        ->  Index Scan using customers_pkey on customers ...
Planning Time: 0.3 ms
Execution Time: 0.9 ms

891ms down to 0.9ms — roughly a 1,000x improvement, entirely from adding the correctly-designed index, with zero application code changes needed. The query plan now shows Index Scan using the new composite index, examining only the 340 rows that actually match, exactly as Chapter 6 predicted this class of fix would achieve.

Step 4: Check for the N+1 pattern at the application layer

[SENIOR INSIGHT] A fixed query plan doesn't guarantee a fast endpoint — if this query is called once per customer in a loop somewhere upstream (rendering a dashboard for a list of customers, say), the endpoint is still slow overall due to Chapter 5's N+1 pattern, even though each individual query is now fast. Checking application-level logs or query counts per request (many frameworks provide a query-count-per-request debugging tool) for this specific endpoint is the next diagnostic step, verifying the fix actually resolved the user-facing symptom, not just the isolated query's own execution time.

Step 5: Confirm under realistic, production-like load

[IN PRACTICE] A fix verified against EXPLAIN ANALYZE on a quiet database can still behave differently under real concurrent production load — verifying the fix with actual monitoring (query latency percentiles, not just a single manual test run) after deployment closes the loop and confirms the theoretical fix translated into an actual, measured production improvement.

Common mistakes

1. Making a change (adding an index, rewriting a query) without first running EXPLAIN ANALYZE to confirm the actual root cause — this risks fixing a problem the query doesn't actually have, while leaving the real cause untouched.
2. Stopping at "the query plan looks better now" without verifying the actual, measured execution time improved, and without checking the change didn't introduce a new problem elsewhere (e.g., slower writes from the new index, per Chapter 6's write-cost trade-off).
3. Fixing the query in isolation without checking for an N+1 pattern at the call site — a fast individual query can still produce a slow overall endpoint if it's called far more times than necessary.
4. Not verifying the fix under real, production-scale concurrent load, missing potential differences between an isolated diagnostic run and actual production behavior.

Discussion / interview questions

1. "Walk me through how you'd diagnose and fix a slow database query in production, step by step." Strong answer: Runs EXPLAIN ANALYZE first to identify the actual bottleneck (rather than guessing), identifies whether it's a missing index (Seq Scan with many rows filtered out), designs an appropriately-ordered composite index if needed, verifies the fix with EXPLAIN ANALYZE again, checks for N+1 patterns at the application layer, and confirms the fix under real production load and monitoring — the full sequence from this chapter.

2. "A query's EXPLAIN output shows a Seq Scan with a very high 'Rows Removed by Filter' count. What does this tell you, and what would you do?" Strong answer: Indicates the database is scanning far more rows than actually match the query's filter, examining and discarding most of them — the classic missing-index signature; the fix is typically adding an index on the filtered column(s), ordered appropriately if the query uses a composite filter.

3. "You've optimized a slow query's execution time by 1000x, but the endpoint that calls it is still slow. What would you check next?" Strong answer: Checks whether the query is being called multiple times per request (an N+1 pattern) rather than once, using per-request query-count monitoring or logging — a fast individual query doesn't guarantee a fast endpoint if it's called far more often than necessary.

4. "Why would you verify a query fix under production-like load rather than trusting a single EXPLAIN ANALYZE run?" Strong answer: A single manual run against a quiet database may not reflect behavior under real concurrent load (lock contention, cache eviction pressure, resource contention with other queries) — production monitoring after deployment confirms the fix translated into an actual, measured improvement, not just a theoretical one.

Chapter summary / cheat sheet

• The complete diagnostic sequence: EXPLAIN ANALYZE to identify the actual bottleneck → design an appropriately-ordered index based on the query's filter pattern → verify with EXPLAIN ANALYZE again → check for N+1 patterns at the application/call-site layer → confirm the fix under real production load and monitoring.
• A Seq Scan with a high "rows removed by filter" count is the standard, recognizable signature of a missing-index problem.
• Fixing an individual query's execution time doesn't guarantee a fast overall endpoint — always check whether the query is being called more often than necessary (N+1).
• Never skip the "measure before and after" discipline — diagnosing by guesswork risks fixing a problem that doesn't exist while leaving the real one untouched.

---