A GraphQL server replaced its batch loaders with one wide join and got slower — how do you diagnose it?
answer
- One round trip cannot cost that much
- Rows read versus objects returned
- Three causes: multiply, discard, project
- Where does the filter actually run
- Prove it by holding the workload fixed
basics
~20 sCompare rows the store read against objects the response contained. A large gap means rows were multiplied by sibling joins or discarded after the fetch — typically by a filter that runs in a field resolver instead of inside the statement.
solid answer
~50 sStart with one ratio: **rows read versus distinct objects returned**, per operation. Removing a round trip can only save a round trip, so a slowdown means the join is reading or shipping far more than the response needed, and there are only three sources. First, multiplication — two one-to-many children joined at once produce their cross product. Second, discard — rows fetched and then dropped in memory, the classic case being an entitlement or visibility filter applied in a field resolver rather than as a predicate in the statement. Third, projection — a join's column list is fixed when the statement is written, so wide parent columns ship on every child row whether the document selected them or not. Measure bytes transferred and rows read alongside latency, identify which of the three dominates, and fix that one. Do not start with the plan; the waste is visible in cardinality before any tuning question arises.
code
pseudocode · 12 lines# Before: the predicate runs after the rows are materialised
rows = store.query("select t.*, s.* from trial t join site s on s.trial_id = t.id
where t.id = any(:trialIds)") # 380,000 rows read
sites = groupByTrial(rows)
resolveSites(trial, ctx) =
sites[trial.id].filter(s -> ctx.viewer.maySee(s)) # 8,730 survive
# After: the predicate is part of the fetch
rows = store.query("select t.*, s.* from trial t join site s on s.trial_id = t.id
where t.id = any(:trialIds)
and s.site_id = any(:visibleSiteIds)") # ~9,000 rows read
resolveSites(trial, ctx) = sites[trial.id]go deeper
Focus on the instinct: fewer queries is not automatically faster, because one statement can read far more rows than the response needs. Knowing to compare rows fetched against objects returned is the takeaway.
Be able to name the three sources of the gap — cross-product multiplication, rows discarded after the fetch, and a fixed wide projection — and describe a concrete check that tells them apart.
Show a disciplined method: measure first, make the cause falsifiable with a controlled comparison, fix the dominant cause, then re-measure the ratio rather than declaring victory on latency alone.
Own the systemic reading. A filter living in a resolver instead of the fetch is a correctness boundary the platform should not allow twice, so the durable fix is a convention and a check, not one patched statement.
## Frame the regression correctly Collapsing two statements into one can save, at most, one round trip — sub-millisecond inside a datacentre. If latency went *up*, the join is doing more work or moving more data than the two statements did, and the diagnosis is about cardinality and volume, not about tuning. Resist the pull toward plans and indexes as a first move; those are a different subject, and here they would be a treatment for a symptom. ## Step 1 — measure the gap Instrument two numbers per operation and compare them: - **rows read** by the store for the statements this operation issued; - **distinct objects** present in the response. On a healthy fetch the ratio sits near 1. In one clinical-trial registry, a page of 25 trials with their sites came back with 8,730 site objects in the response — and the store had read 380,000 rows to produce them. A ratio near 43 is not a tuning problem; it is 97% of the fetch being wasted. Add **bytes transferred** to the same view. Rows and bytes diverge when the parent columns are wide, and the difference tells you whether you are fighting row count or row width. ## Step 2 — split the gap into its causes There are three, and they need different fixes. **Multiplication.** Two one-to-many children joined to the same parent produce their cross product. Test it directly: count distinct child ids in the result versus total rows. If distinct sites are 8,730 and rows are 380,000, and the statement also joins arms, the arm fan-out is your multiplier. **Discard.** Rows arrive and are thrown away in memory. In the registry case this was the cause: the site list was filtered by the caller's entitlement, and that check ran inside the `sites` field resolver, after the rows had already been read, transferred and materialised. Under loaders it had been just as wrong, but the loader's `in (...)` lookup was narrow enough that nobody noticed; the wide join turned a hidden inefficiency into a visible one. The tell is a large drop between rows materialised and objects serialised, concentrated in one field's code path. **Projection.** The join's column list is authored once and does not shrink with the document. A document selecting two parent fields still ships all fourteen, once per child row. Compare the columns in the statement against the fields the operation actually returned; narrowing a projection from the selection set is a separate technique, but you can see the waste without it. ## Step 3 — confirm before you change anything A slow endpoint attracts guesses, so make the cause falsifiable: - Run the same operation with the discarding predicate moved into the statement and compare rows read. If 380,000 drops to roughly 9,000, discard was the cause. - Run it with one child list removed from the join. If rows collapse by the other child's fan-out, multiplication was the cause. - Keep the loader path behind a flag and run both on a small share of traffic for a day, comparing rows, bytes and p99 on identical operations. This is the only comparison that survives an argument, because it holds the workload fixed. ## Step 4 — fix the dominant cause - **Discard**: push the predicate into the statement so the store never returns those rows. This is a correctness improvement as much as a performance one — a filter that runs after the fetch has already exposed the rows to every layer in between, and it is far easier to forget on a new code path than a predicate that lives in the fetch itself. - **Multiplication**: give each one-to-many edge its own result set. - **Projection**: narrow the statement, or accept the width and go back to a shape where the parent row travels once. Then re-measure the same ratio. "Latency improved" is weak evidence on its own; "rows read per object returned fell from 43 to 1.2" is the thing that will still be true next month. ## Step 5 — decide whether the join should exist at all Sometimes the honest answer after diagnosis is that the join was never the right shape for this edge — wide parent, large fan-out, a per-caller predicate and a per-parent limit are four arguments for a separate keyed lookup, and the single round trip it saves is worth less than any one of them. Reverting is a legitimate outcome, and having the measurements makes it an evidence-backed decision rather than a retreat. ## What not to conclude Do not report this as a database problem. The store did what it was asked; the request asked for 380,000 rows. Whether the engine chose a good way to produce them is a real question with its own owner, and it is downstream of the fact that the rows should never have been requested.
- The team argues the join is still better because it issues fewer statements. How do you answer?Statement count is a proxy, and a poor one here. What matters is rows read, bytes moved and time on the wire, and the join was losing on all three by a factor of tens. I would put the two numbers side by side from the same traffic: statements per operation, and rows read per object returned. Fewer statements is worth roughly one round trip; forty times the rows is worth far more. If the numbers were close, statement count would be a reasonable tiebreak.
- Why is a filter running inside a field resolver a problem beyond performance?Because the rows have already left the store and passed through every layer in between, so the restriction depends on one code path continuing to apply it. Any new path that reads the same fetched data — an aggregate field, a cache warm, an export — silently sees the unfiltered set. A predicate in the fetch is enforced once, in one place, for every consumer. The performance gap is often what makes the team look, but the placement is the real defect.
- How do you tell multiplication apart from discard without changing the code?Count distinct child ids in the joined result and compare against total rows. If the ids are distinct and there are far more rows than ids, something is multiplying them — look for a second one-to-many table in the join. If total rows and distinct ids are close but both far exceed the objects in the response, the rows are being discarded after the fetch. Both can be true at once, so measure both before attributing the whole gap.
saying these in an interview costs you the question
- Jumps straight to indexes or query-plan tuning
- Assumes fewer statements always means lower latency
- Measures only latency, never rows or bytes
- Leaves a visibility filter in the field resolver
- Blames the database for rows the service requested
- Reverts to loaders without measuring which cause dominated