Query Optimization in Practice

Your marketplace's order dashboard takes 646 ms to load, and the pager is starting to notice. You do what How a Query Executes taught you: pull a query from the endpoint and EXPLAIN ANALYZE it.

Index Scan using customers_pkey on customers  (cost=0.29..8.31 rows=1)
Execution Time: 0.053 ms

Perfect plan. Index scan, one row, a twentieth of a millisecond. You check another query from the same endpoint — also perfect. Every query this endpoint runs is fast, and the endpoint is slow.

So where did 646 milliseconds go?

That question is this lesson. The answer — the time isn't in the queries, it's between them — is the single most common way applications make a fast database slow, and Connection Pooling for Databases already named it on the way out: a thousand tiny queries where one would do. The N+1.

In this lesson: where the time actually hides (measured to the millisecond), why your EXPLAIN-trained instincts can't see it, the two real fixes and how to choose between them, the famous case where the fix itself backfires, and how teams keep the bug from coming back.

Scope: you've met N+1 twice before — as a GraphQL resolver pathology in GraphQL: Ask for Exactly What You Need and as latency arithmetic in Numbers to Know. This lesson is where it actually lives: in any ORM loop, in any plain REST app, invisible in your own code. Designing indexes stays in Indexes Deep-Dive; the planner's cost model stays in How a Query Executes; and the one fix that involves a cache is the next lesson, Caches & Databases: Read-Your-Writes Coherence.

EXPLAIN looks at one query. To find this bug you have to look at the request. Turn on query logging (or open your APM's trace view) and the picture changes completely:

SELECT id, customer_id, total FROM orders ORDER BY created DESC LIMIT 500
SELECT name, city FROM customers WHERE id = 84121
SELECT name, city FROM customers WHERE id = 3355
SELECT name, city FROM customers WHERE id = 67902
… 497 more, identical but for the id

One query for the page. Then five hundred queries, one per row. 1 + N — hence the name.

Now the accounting. We ran exactly this against a real Postgres 16 — 100,000 customers, 1,000,000 orders — and asked pg_stat_statements what the database actually did during that one request:

 calls | mean_ms | total_ms | query
   500 |   0.010 |      5.1 | SELECT name, city FROM customers WHERE id = $1
     1 |   2.019 |      2.0 | SELECT id, customer_id, total FROM orders ORDER BY …

Read that middle column again. The database worked for 5.1 milliseconds of a 646-millisecond request. Less than one percent. The other 99% is five hundred network round trips — request out, response back, your turn, your turn, your turn — each one paying the toll Numbers to Know put on every hop.

The toll itself is small, which is exactly why the bug is invisible until it isn't. Measured, per round trip: about 0.11 ms to a database on the same box, ~0.4 ms in the same availability zone, ~1.3 ms one zone over. Multiply by 500 and the same innocent loop costs 55 ms, 203 ms, or 646 ms — the bug's size is set by where your database lives, not by anything in your code. And notice what happens at N=2,000: 2.67 seconds, of which the database worked about 20 ms. The endpoint's cost scales as N × round-trip; the work scales as N × ten microseconds.

Here's the code that fired those 501 queries:

orders = Order.recent(limit=500)        # 1 query
for o in orders:
    render(o.total, o.customer.name)    # ← looks like a field access

There is no SELECT in that loop. o.customer looks like reading a field. But customer is a relation, and every mainstream ORM — ActiveRecord, Django, Hibernate, SQLAlchemy — loads relations lazily by default: querying Order fetches only orders, and the first time you touch o.customer, the ORM quietly runs SELECT … WHERE id = ? on your behalf. Touch it in a loop and you've written a query-per-row program without writing a single query. The loop is the author.

The ORM isn't being stupid — it's being general. At query time it cannot know your template will touch .customer on every row; loading every relation eagerly for everyone would be worse (hold that thought — it's the backfire section). Lazy is the right default with a sharp edge, and the edge has a shape you've seen before: in GraphQL: Ask for Exactly What You Need, independent resolvers each fetched their own author and nobody wrote bad code there either. Same pathology, different disguise. There the fix was DataLoader batching resolver calls; here you'll meet the same fix wearing SQL.

One more property makes this the classic production ambush: dev can't reproduce it, by construction. Your dev database has ten orders. 1 + 10 queries over localhost's 0.1 ms toll is two milliseconds — every test green, every page instant. One production account has 40,000 orders, and a documented incident tells the rest: 40,001 queries in one request, roughly a minute of wall time — while holding one pooled connection the entire time. A few of those requests in flight and the pool from Connection Pooling for Databases is drained; endpoints that never touch orders start timing out, and the incident channel is debugging 'the database is slow' while the database sits nearly idle. N is small in dev and unbounded in prod — code that is correct is not the same as code that is correct at scale.

Everything §4 has taught so far will tempt you here, and none of it works. It's worth feeling why, one instinct at a time.

"Add an index." Look at the EXPLAIN again: Index Scan using customers_pkey … 0.053 ms. These queries are primary-key lookups — they already run at index speed. An index makes each of the N queries faster; it cannot make there be fewer of them, and 0.053 ms of execution inside a 1.3 ms round trip means the part an index can help is already noise. Indexes Deep-Dive fixes a slow query. This is a slow endpoint made of fast queries.

"Add a replica." The Read-Scaling Playbook's diagnostic question — is the database slow, or busy? — lands hard here. This database is neither: it's bored, working 5 ms out of every 646. A replica adds lanes; this one car is stopping at 500 toll booths. Worse, replicas sit behind the same network toll — routing these 500 queries to a replica changes nothing about their count.

"Raise the pool size." The pool is the victim. Each N+1 request holds one connection for 646 ms to do 5 ms of work — at a pool of 20, that's a hard ceiling of ~31 requests/second for the whole application. A bigger pool lets more requests chat idly at once and marches you toward the max_connections wall from Connection Pooling for Databases. Fix the chatter and the same pool carries thousands: the JOIN version holds its connection for 3 ms — a ceiling of ~6,300 req/s. Two hundred times more throughput from the same hardware, which is the difference between capacity problems you buy your way out of and work problems you engineer your way out of.

The pattern behind all three: they optimize a query or add capacity, and the N+1 is neither a slow query nor a capacity problem. It is a shape problem — the conversation between your app and your database has the wrong shape.

SQL is a set-oriented language. The N+1 is what happens when you feed it rows one at a time — five hundred trivial questions the planner can only answer one by one. Hand it the set and everything How a Query Executes taught — join algorithms, the cost crossover, all of it — finally has something to work with. "Respect the planner" means exactly this: ask one declarative question and let it plan, instead of starving it with trivia. The loop belongs in the database.

There are two ways to hand it the set, and every ORM ships both:

Fix 1 — one JOIN. Fold the relation into the page query:

SELECT o.id, o.total, c.name, c.city
FROM orders o JOIN customers c ON c.id = o.customer_id
ORDER BY o.created DESC LIMIT 500;

One query, one round trip. Measured: 3.17 ms where the loop cost 646.5 — 136× faster.

Fix 2 — a second query with IN. Keep the page query, then fetch all the customers at once:

SELECT id, name, city FROM customers WHERE id IN (84121, 3355, …);

Two queries, two round trips. Measured: 4.76 ms. This is DataLoader's exact move from the GraphQL lesson — collect the ids, fire one batched query — implemented in plain SQL.

Your ORM has both behind one method call; the whole fix is usually one line:

Which one? The rule the ORMs themselves encode: many-to-one → JOIN; one-to-many → IN-batch. Each order has exactly one customer, so the JOIN adds columns without adding rows — clean. When the relation is a collection, the JOIN starts multiplying rows, and that story gets its own section below, because it's where the confident fix goes wrong.

One more measured property, easy to miss: batching is also distance insurance. As the round-trip toll grew 12× in our capture (same box → next AZ), the JOIN went from 2.04 ms to 3.17 ms — it pays the toll once. The N+1 went from 55 ms to 646 ms. Move your database, change your deployment topology, and the batched endpoint barely notices; the loop rediscovers geography. And a subtle bonus from Hibernate's world: fetch strategy is per-query, not per-schema. Marking a relation eager everywhere just moves when the extra queries fire (and drags relations you didn't need). Declare what this screen needs on this query — the same lesson Indexes Deep-Dive taught about indexes modeling the queries.

You're on call. Below are the two tickets of this lesson, live. Ticket #1 is the 646 ms mystery: open the tools (the trace, the EXPLAIN, pg_stat_statements, the code), find where the time is, then try the fixes — including the tempting wrong ones — with the page size and database distance under your control. Watch the pool ceiling while you're at it. Solving it unlocks Ticket #2, where the fix that just worked has a failure mode. Try to notice it before the meters tell you.

Ticket #2's dashboard wants 100 posts, each with its 10 comments and its 5 tags. Fresh from ticket #1, the instinct is obvious: one query for everything. Posts JOIN comments JOIN tags. What could be wrong with fewer round trips?

A JOIN doesn't attach collections — it multiplies rows. Joining posts to comments repeats each post 10 times; joining that to tags repeats each of those rows 5 times. Every post comes back 50 times, every comment body 5 times. The result is the product of your collections, and we measured it:

"One query" lost to three queries, while paying three round trips, because it shipped three times the rows and six times the bytes. The ORM will even dedup the repeats for you in memory, so the bug is invisible in the result — you pay in transfer, parse, and RAM. Scale the idea up and you get a documented war story: a team fixed an 847-query dashboard by scattering eager-loads everywhere — queries dropped to 23, and memory went from 180 MB to 2.1 GB per request because they were loading 40,000 associated records their views never displayed. Twelve-second page loads, from the fix.

And the quietest failure of the three, straight from our capture: pagination. LIMIT 100 on the joined result limits exploded rows, not posts — the real query came back with 10 distinct posts. Your "page of 100" is a page of 10, no error anywhere. (This is why ORMs contort into subqueries when you combine joined eager-loading with LIMIT.) The IN-batch fix composes with pagination naturally: LIMIT applies to posts, collections are fetched per page.

So the rule from ticket #1 gets its second clause: count round trips first, then count rows. One query is not the goal — bounded work is: O(1) queries per request, each returning the sum of what you need, never the product.

Before you go stamping out every per-row query in sight, you should hear the best argument on the other side, because it comes from someone with receipts.

DHH — Rails' creator — has long argued that in Basecamp, N+1 queries are a feature. The trick is what Rails calls Russian-doll caching: every item on the page (each email, each card) is a cached fragment with its own key and its own expiry. Fifty emails means fifty tiny queries the first time — and almost none after, because most requests find fifty warm fragments and touch the database for the handful that changed. One big JOIN, by contrast, recomputes everything whenever anything changes. Basecamp ran sub-100 ms on this diet, and their own cache-key analysis backed it: 90% of keys were net positive, improving responses by an average of 71 ms.

It's a real result, and worth respecting. Also worth reading precisely: the defense never claims N round trips are cheap — it claims you'll rarely pay them. Every part of it stands on the cache. On a cold cache, after a deploy, on the long tail of rarely-viewed pages, the ladder of round trips is right there waiting, plus a stampede of misses (Ayende's rebuttal in the famous back-and-forth). And the moment you keep per-item copies of database rows, you've bought a new problem: copies that can disagree with the truth.

Which is not a problem this lesson can price. It's the next one — Caches & Databases: Read-Your-Writes Coherence. Keep DHH in mind as you read it: his trade is real, and so is its bill.

You'll fix this bug more than once, because every new loop over a relation is a fresh chance to write it. Mature teams stop relying on vigilance and wire the detection in.

In development — make lazy loading loud. Rails 6.1 shipped strict_loading (contributed by GitHub's Eileen Uchitelle): touch an association that wasn't eager-loaded and instead of a silent query you get ActiveRecord::StrictLoadingViolationError — per record, per model, or app-wide, with a log-only mode for production. Sit with what that is: the framework's own answer to its own default is a kill switch. That's how pervasive this bug is. Laravel ships the same idea (Model::preventLazyLoading()); Rails' Bullet gem and Django's ecosystem (django-zeal, assertNumQueries in tests) watch for the pattern in dev and CI.

In production — count queries per request. The APM trace signature is unmistakable once you've seen it: one span, then a ladder of N identical short spans. Most APMs (Sentry, Scout, Datadog) now flag it automatically. The metric worth alerting on is queries per request: healthy endpoints are single-digit and constant; an N+1 endpoint's count scales with data.

On the database — read the shape of pg_stat_statements. You saw it at the crime scene, and it generalizes into a diagnostic you'll use for years:

• huge calls, tiny mean_exec_time → an N+1. The database is fine; the conversation is wrong. Fix the app's query shape (this lesson).
• small calls, large mean_exec_time → a genuinely slow query. Now EXPLAIN it — you're back in Indexes Deep-Dive and How a Query Executes territory, where that toolkit actually works.

calls × mean is the real bill either way. Knowing which column is screaming tells you which lesson to open.

Is every N+1 a fire? No — and pretending otherwise costs credibility under follow-up.

When it's genuinely fine: N is small and provably bounded — a settings page with 6 sections, a nav bar with 5 items. Six round trips at 0.4 ms is 2.4 ms; batching it is polish, not rescue. Or the per-item reads sit behind a hot cache (DHH's world) and you've knowingly bought the coherence bill. On a laptop, remember, our 500-query loop cost 91 ms — locally this bug barely exists, which is exactly why it ships.

The test is the growth law, not today's latency. Ask one question of any loop-over-a-relation: does the query count scale with data? A bounded N is a judgment call. An unbounded N — orders per account, comments per post, anything a user can grow — is a time bomb with a fuse measured in your success. The invariant worth defending in review is O(1) queries per request: the number of queries an endpoint issues should not depend on how many rows it displays.

What you'd say under follow-up: "I'd check queries-per-request in the trace before touching indexes — high-calls-tiny-mean is an app-shape problem, not a database problem. Fix is eager loading: JOIN for to-one, IN-batch for to-many — never one JOIN across sibling collections, that's a row product and it breaks LIMIT. Guard it with strict loading in dev and a queries-per-trace alert in prod. And if someone defends the N+1 with caching, they're not wrong — they've just moved the cost to cache coherence, which has its own lesson."

Everything below ran for real; your numbers will vary in the third decimal, not in the story. You need docker and psql.

1. A database with a million orders:

docker run -d --name nq --cap-add=NET_ADMIN -e POSTGRES_PASSWORD=pw -p 55448:5432 postgres:16
export PGPASSWORD=pw
psql -h localhost -p 55448 -U postgres <<'SQL'
CREATE TABLE customers(id bigint PRIMARY KEY, name text, city text);
INSERT INTO customers SELECT g, 'customer-'||g,
  (ARRAY['Paris','Berlin','Madrid'])[1+g%3] FROM generate_series(1,100000) g;
CREATE TABLE orders(id bigserial PRIMARY KEY,
  customer_id bigint REFERENCES customers(id), total numeric(10,2), created timestamptz);
INSERT INTO orders(customer_id,total,created)
  SELECT 1+(random()*99999)::int, (random()*400+5)::numeric(10,2),
         now()-(random()*interval '365 days') FROM generate_series(1,1000000);
CREATE INDEX ON orders(created DESC);
VACUUM ANALYZE;
SQL

2. Build the N+1 as a file — 500 customer lookups, exactly what the ORM loop fires — then race it against the JOIN. psql -f sends statements one at a time: one round trip each, just like the app:

psql -h localhost -p 55448 -U postgres -Atc "SELECT 'SELECT name, city FROM customers WHERE id = '||customer_id||';' FROM orders ORDER BY created DESC LIMIT 500" > nplus.sql

time psql -h localhost -p 55448 -U postgres -q -f nplus.sql -o /dev/null
time psql -h localhost -p 55448 -U postgres -q -c "SELECT o.id, o.total, c.name, c.city FROM orders o JOIN customers c ON c.id=o.customer_id ORDER BY o.created DESC LIMIT 500" -o /dev/null

Predict first: how much faster is the JOIN on your laptop? Our run:

N+1 (500 statements):  real  0m0.091s
JOIN (1 statement):    real  0m0.045s

Twice as fast. Honestly? Underwhelming. 91 ms wouldn't page anyone — your laptop cannot show you this bug, because localhost's round-trip toll is microscopic. Which is why it always ships.

3. Now move the database one availability zone away — one added millisecond, the kind of distance every real deployment has — and run the exact same two commands:

docker exec nq bash -c "apt-get update -qq && apt-get install -y -qq iproute2 && tc qdisc add dev eth0 root netem delay 1ms"

N+1 (500 statements):  real  0m0.703s   ← 7.7× worse
JOIN (1 statement):    real  0m0.052s   ← didn't notice

One millisecond of distance turned 91 ms into 703 ms — and the JOIN paid it once: +7 ms. That's the whole lesson in two lines of time output: the N+1's cost is N × distance; the fix's cost is distance.

4. Clean up: docker rm -f nq

• "Every query is fast, so the endpoint is fast." The cost is per round trip, not per query — N × toll dominates. Measured: 5.1 ms of database work inside a 646 ms request. The time is between the queries.
• "EXPLAIN will show me the problem." EXPLAIN examines one query, and every one of the N is individually perfect (0.053 ms, index scan). Detection lives at request granularity: the query log, the APM trace, pg_stat_statements.calls.
• "The fix is an index." Indexes make each of the N queries faster; the N remains. A slow query is Indexes Deep-Dive's problem; a slow endpoint made of fast queries is this lesson's.
• "Add a replica / bigger box." The database is bored, not busy — capacity multiplies how many requests you can serve, not how fast one request's 500 sequential round trips finish.
• "Fewer queries is always better." One mega-JOIN across two collections shipped 3× the rows and 6× the bytes of three queries and ran 2× slower — and its LIMIT returned 10 posts instead of 100. Bounded work is the goal, not query count.
• "Make everything eager by default." Eager-everywhere is the 180 MB → 2.1 GB war story, and in JPA an EAGER mapping doesn't even fix JPQL queries. Fetch strategy is per-query: declare what this screen needs.
• "The ORM is the villain — write raw SQL." The same loop of SELECTs ships in raw-SQL codebases every day. The ORM hid the round trips, but it also carries the fix in one method call. The villain is the loop.
• "My laptop says it's fine." Your laptop's round-trip toll is ~0.1 ms; production's is 4–13× that. Measured: the same loop, 91 ms local, 703 ms one zone over. Distance is the multiplier, and dev has none.

• The N+1: one query for the page, then one per row — written by a loop touching a lazily-loaded relation, visible in no single query. The endpoint is slow; every query is fast; the time is between the queries (measured: 5.1 ms of DB work in a 646 ms request).
• Cost model: N × round-trip toll (0.11 ms same box / ~0.4 ms same AZ / ~1.3 ms next AZ, measured). Dev hides it (tiny N, tiny toll); production multiplies it; the pooled connection is held the whole time (#34's pool, drained: ~31 req/s vs ~6,300 on the same 20 connections).
• The fixes hand the planner one set-oriented question: JOIN for to-one relations (1 query, 3.17 ms, 136×), IN-batch for to-many (2 queries, 4.76 ms — DataLoader in SQL). One line in any ORM: includes / select_related + prefetch_related / joinedload + selectinload / JOIN FETCH.
• The backfire: a JOIN across sibling collections returns the product (5,000 rows / 431 KB vs 1,600 / 72 KB, 2× slower) and breaks LIMIT (100 → 10 posts). Count round trips first, then count rows: O(1) queries per request, sum-shaped results, never products.
• Diagnose by shape: huge calls × tiny mean = N+1 (fix the app); small calls × big mean = slow query (fix the query). Guard with strict loading in dev and queries-per-trace in prod.
• The honest defense of N+1 (DHH's Russian-doll caching, sub-100 ms Basecamp) stands entirely on a cache — and copies that can disagree with the truth are their own problem. Next — Caches & Databases: Read-Your-Writes Coherence: the fastest read in the system, and what it costs to keep it honest.