A recorded plan is triggered twice for two different outputs and the second run takes as long as the first — why?

This is the second price of plan-then-run execution, and it catches people precisely because the handle *feels* like data. ## A plan is a recipe, not a meal The object you are holding is a description: *from this source, keep these rows, add this derived column, group and total*. Triggering it hands that description to the engine, which reads the input and produces an answer. The answer is returned to you. Nothing is stored back into the description — it is exactly what it was before, a set of instructions. So the second trigger is not a resumption and not a cache hit. It is a fresh execution of the same recipe, from the source, and it costs what the first one cost. If the two triggers differed only in the last step, every step before that last one was performed twice. ## Why a design would behave this way Retaining results by default would be the wrong default for the workload deferral is aimed at. The whole point of recording steps is that the intermediate results are often much larger than the answer, and frequently larger than memory; a design that silently kept every intermediate would defeat the footprint saving that made deferral attractive. So the contract is: you get the answer, and you decide what is worth keeping. ## Three ways out 1. **Retain the shared prefix.** Trigger the chain once up to the point the two outputs diverge, hold that result as an ordinary in-memory object, and derive both outputs from it. Cost: the shared result is now resident for as long as you hold it, which is the footprint you deferred in order to avoid. 2. **Express both outputs in one plan.** Where the design can produce several results from one recorded chain, the shared prefix is traversed once and both branches are computed from it in the same run. Cost: none in traversals, but the shared part still has to be held while the second branch consumes it, and not every design offers this. 3. **Write the shared intermediate out, then read it back.** Persist the result of the shared prefix to a local file and point each consumer at that file. Cost: one write and two reads instead of two full runs — usually the right trade when the shared prefix is expensive to compute and cheap to store, and the only option when it does not fit in memory. | approach | source read | shared work done | extra resident bytes | |---|---|---|---| | trigger twice | twice | twice | none | | retain the shared prefix | once | once | the shared result, held | | one plan, two outputs | once | once | the shared result, while consumed | | write out and read back | once, plus two reads of the intermediate | once | none, but disk used | ## Diagnosing it The symptom is distinctive: two answers that overlap heavily in their derivation, and two run times that are nearly equal rather than one long and one short. Compare the elapsed time of the second trigger against a run of the shared prefix alone. If they are close, the prefix is being redone. Reading the operating system's counters for bytes read from the source over each run makes it unambiguous — the source is being read once per trigger. Watch for the same effect arriving by accident. Asking a deferred handle for a row count, printing the first few rows to check the transform, and then triggering the real answer is three runs of the chain, not one plus two cheap peeks. Interactive work is where this cost is most often paid without noticing, because each individual wait feels tolerable. ## What varies between designs Some designs offer an explicit way to say "keep this result and reuse it for later triggers", which turns the recipe into something closer to a held value. Some can plan several outputs together. Some will reuse work within a single trigger when the same sub-chain appears twice in one plan, but not across triggers. And some retain nothing at all under any circumstances. The behaviour to assume in an interview is the conservative one — a trigger executes the chain and retains nothing — and then to say that particular designs offer a way to opt into retention, which is the thing to look for before hand-rolling a write-out-and-read-back. The underlying judgment is the one that generalises: deferral trades resident bytes for traversals. Retaining results trades the other way. Neither is free, and the shape of the work decides which side you want to be on.