When a walk touches a deferred collection inside another deferred collection, why does the statement count multiply?

One list read that becomes a statement per row is annoying. Two levels of that, nested, is the version that takes an endpoint from fast to unusable, and the reason is arithmetic: nested walks **compose by multiplication**, not by addition. ## How the levels compose Suppose an operation reads a page of parents, then for each parent walks a deferred child collection, then for each child touches a deferred link of its own. 1. **The list read** — one statement, returning `P` parent rows. 2. **The first walk** — the child collection of each parent is touched once, so `P` statements. 3. **The second walk** — a link is touched on every child that the first walk produced. If each parent has on average `C` children, that is `P x C` statements. Total: `1 + P + (P x C)`. With 50 parents of 4 children each that is 1 + 50 + 200 = 251 statements for what the code reads as "render a page of 50 things". Add a third level and the next term is `P x C x G`. The mistake worth naming is reading this as `1 + P + C`. The second level does not run once for the whole page; it runs once per row the level above produced. Nesting multiplies because iteration nests. ## Why a one-line change can shift an order of magnitude The deepest term dominates, and the deepest term is created by whichever field is touched inside the innermost collection already being iterated. So: - Adding one linked field to what is rendered adds a whole term, not one statement. - Removing one linked field can delete a term outright, which is why bisecting the output shape is such an effective diagnostic. - Two fields at the same depth **add** to each other (`P x C x 2`); one field a level deeper **multiplies**. | Change to the output shape | Effect on the total | Why | |---|---|---| | Second linked field on the parent | `+ P` | another walk at the same depth | | First linked field on the child | `+ (P x C)` | a new term, one per child | | Second linked field on the child | `+ (P x C)` | same depth, so it adds | | A collection under the child | `+ (P x C x G)` | a new, deeper term | | Page size doubled | every term with `P` doubles | the multiplier itself grows | ## The count is a property of the data `C` is not in the code. It is however many children each parent happens to have in the data set being served, so the same path emits 15 statements against a hand-made data set and 15,000 against a real tenant. Two consequences follow. First, **averages hide the tail**. If most parents have two children and a handful have four thousand, the mean total is unremarkable while individual requests are catastrophic. The distribution of `C`, not its average, sets the worst case. Second, **paging bounds only the outer term**. Capping the page at 50 fixes `P`, but `C` and anything deeper stay unbounded unless the walk itself is bounded. A page-size limit is often mistaken for a guarantee on statement count; it is a guarantee only on the first multiplier. ## Reading the nesting out of a log A nested walk has a recognisable rhythm rather than a flat run. You see the list read, then a statement for one parent's collection, then a burst of statements for that parent's children, then the next parent's collection, and so on — the pattern repeats in blocks whose size is that parent's child count. A single-level walk, by contrast, is one uniform run. Counting the statements between two consecutive occurrences of the parent-level statement gives you `C` for that parent directly, and unequal block sizes are the visible fingerprint of skew in the data. Ordering can also invert: some layers, when a link is touched, take the opportunity to fill that same link for other loaded objects at once, so a level that would have produced a statement per row produces a handful of grouped statements instead. When that happens the block rhythm flattens and the term stops scaling one-for-one with rows — which is worth recognising in a log, because it changes which level is actually the dominant one. ## The judgment it tests Asking about nesting separates candidates who have memorised "a query per row" from those who can predict a total from a data shape. The second group asks how many parents, how many children each, how deep the walk goes, and which of those numbers is bounded by anything — before touching the code at all.