Chapter 5: SQL Essentials and Query Optimization

Why this matters

Writing SQL that returns the correct result is table stakes; writing SQL that returns the correct result efficiently, at scale is a separate skill most engineers pick up only after being burned by a production slowdown. This chapter covers reading a query plan — the single most valuable diagnostic skill for query performance — and the N+1 query problem, likely the most common, entirely avoidable performance bug in database-backed applications.

Joins, briefly and concretely

[BEGINNER] A join combines rows from two or more tables based on a related column — the mechanism that lets normalized data (Chapter 2) be reconstructed into a complete picture for a query:

SELECT orders.id, orders.total_cents, customers.email
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.email = 'alice@example.com';

An INNER JOIN (the default JOIN) returns only rows with a match in both tables; a LEFT JOIN returns every row from the left table, with NULLs for any unmatched right-table columns — the right choice depends on whether "no match" should exclude a row entirely or just leave some columns empty.

Reading a query plan with EXPLAIN

[BEGINNER] EXPLAIN shows how PostgreSQL intends to execute a query — which is the single most important tool for understanding why a query is slow, rather than guessing:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Without an index (a full table scan):

Seq Scan on orders  (cost=0.00..18334.00 rows=1 width=64) (actual time=45.2..120.8 rows=3 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 999997
Planning Time: 0.15 ms
Execution Time: 121.0 ms

With an index on customer_id:

Index Scan using idx_orders_customer_id on orders  (cost=0.42..8.44 rows=1 width=64) (actual time=0.03..0.05 rows=3 loops=1)
  Index Cond: (customer_id = 42)
Planning Time: 0.10 ms
Execution Time: 0.08 ms

Reading this precisely: Seq Scan means PostgreSQL read every single row in the table and checked each one against the filter — Rows Removed by Filter: 999997 makes the cost of this approach vivid: nearly a million rows examined and discarded to find 3 matching ones. Index Scan instead uses an index (Chapter 6) to jump directly to the matching rows, examining only what's actually needed. The Execution Time difference (121ms vs. 0.08ms — roughly 1,500x) is the entire practical payoff of indexing made concrete, and this exact diagnostic process (run EXPLAIN ANALYZE, look for Seq Scan on a large table where an Index Scan should be possible) is the standard first step for any "this query is slow" investigation.

The N+1 query problem

[SENIOR INSIGHT] The N+1 query problem is the single most common, entirely avoidable database performance bug in application code — it happens when code fetches a list of N records, then executes one additional query per record to fetch related data, resulting in N+1 total queries where one or two would have sufficed.

Before — N+1 queries, a common pattern in naive ORM usage:

orders = db.query("SELECT * FROM orders WHERE customer_id = 42")  # 1 query, returns 20 orders
for order in orders:
    # This line runs a SEPARATE query for EACH of the 20 orders — 20 additional queries!
    items = db.query(f"SELECT * FROM order_items WHERE order_id = {order.id}")
# Total: 1 + 20 = 21 queries, where the data could have been fetched in far fewer.

Each individual query might be fast in isolation, which is exactly why this problem is so easy to miss in development (with a handful of test records) and so damaging in production (with realistic data volumes) — 20 fast queries executed sequentially, each paying its own network round-trip cost (companion Networking book, Chapter 9), add up to real, user-visible latency that a single well-designed query wouldn't incur.

After — one query, using a join:

SELECT orders.id AS order_id, order_items.*
FROM orders
JOIN order_items ON order_items.order_id = orders.id
WHERE orders.customer_id = 42;
-- ONE query, ONE round trip, returns everything needed — application code
-- groups the flat result set by order_id afterward, in memory, which is far
-- cheaper than 20 separate network round trips ever could be.

Or, when a join would produce an awkward, duplicated result shape, a second, batched query:

-- Query 1: get the order IDs
SELECT id FROM orders WHERE customer_id = 42;

-- Query 2: get ALL items for ALL those orders in ONE query, using IN (...)
SELECT * FROM order_items WHERE order_id IN (101, 102, 103, ...);
-- TWO total queries, regardless of how many orders there are — not N+1.

Common mistakes

1. Guessing at the cause of a slow query instead of running EXPLAIN ANALYZE first. This is the single highest-leverage habit change for query optimization — it replaces guessing with a direct, authoritative answer about what the database is actually doing.
2. Writing ORM code that iterates over a collection and triggers a query per item, without realizing this pattern is the N+1 problem — most modern ORMs provide an explicit "eager loading" or "prefetch related" mechanism specifically to collapse this into one or two queries, and not using it is the most common root cause.
3. Not recognizing Seq Scan in an EXPLAIN plan as an actionable signal on a large table where an index could apply — a sequential scan isn't always wrong (for a small table, or a query genuinely needing most rows, it can be the correct plan), but on a large table filtered to a small subset, it's the classic missing-index signature.
4. Testing query performance only against small development datasets, missing that a query pattern's cost (especially N+1's) scales with data volume in ways that are invisible until production-scale data is involved.

Checklist: writing and diagnosing efficient SQL

1. Run EXPLAIN ANALYZE on any query suspected of being slow, before making any changes — identify whether it's doing a Seq Scan where an Index Scan should be possible, or some other inefficiency, rather than guessing.
2. Audit any loop that executes a database query per iteration as a potential N+1 problem — collapse it into a single join or a batched IN (...) query.
3. Use your ORM's explicit eager-loading/prefetch mechanism for any relationship you know will be accessed for every record in a result set, rather than relying on lazy loading's per-record query behavior.
4. Test query performance against realistic data volumes, not just small development datasets, since several classes of query performance problems (N+1 especially) don't manifest meaningfully at small scale.

Discussion / interview questions

1. "How would you diagnose why a specific SQL query is slow?" Strong answer: Runs EXPLAIN ANALYZE on the query first, looking specifically for a Seq Scan on a large table where an index should apply, or other inefficiencies in the plan, rather than guessing at the cause or immediately trying random optimizations.

2. "What is the N+1 query problem, and how would you fix it?" Strong answer: Fetching N records and then executing one additional query per record for related data, resulting in N+1 total queries; fixed by combining everything into a single join query, or a batched query using IN (...) for the related data, reducing the total query count to one or two regardless of N.

3. "Why might the N+1 query problem go unnoticed during development but cause real performance issues in production?" Strong answer: With a small number of test records, N+1 extra queries is a small, fast number; at production scale with many more records, the same pattern executes far more queries, each paying its own network round-trip cost, compounding into real, user-visible latency that doesn't show up meaningfully in small-scale testing.

4. "What's the difference between an INNER JOIN and a LEFT JOIN, and when would you use each?" Strong answer: INNER JOIN returns only rows with a match in both tables; LEFT JOIN returns every row from the left table regardless of a match, with NULLs for unmatched right-table columns — use LEFT JOIN when you need to include left-table rows even when there's no corresponding related data.

Chapter summary / cheat sheet

• EXPLAIN ANALYZE shows exactly how PostgreSQL executes a query — the essential first diagnostic step for any slow-query investigation, revealing Seq Scan (full table scan) vs. Index Scan (targeted lookup) and their actual measured cost.
• The N+1 query problem: fetching N records then querying once per record for related data — fixed with a single join or a batched IN (...) query, collapsing N+1 queries into one or two.
• N+1 and missing-index problems are both easy to miss at small development scale and damaging at production scale — always validate query performance against realistic data volumes.
• Most modern ORMs provide explicit eager-loading mechanisms specifically to prevent N+1 — using them deliberately, rather than relying on default lazy-loading behavior, is the standard prevention.

---