Caching and memoization of subplans
When does sharing a sub-result across queries pay, and why does that decision need the query log?
Inside one query, sharing a sub-result (common sub-expression elimination) needs no invalidation: the snapshot of the data is fixed and cannot move. Across queries, the scope change alters the economics. Sharing needs invalidation (knowing when stored results become wrong, covered in the result-caching section) and matching (recognizing that a stored result answers a new query).
The break-even inequality is the same as before:
cost(compute) · (k − 1) > cost(store) + Σᵢ cost(adapt to consumer i)
where k is the number of consumers. But k is now unknown when you decide. You are not measuring a query. You are betting on future queries. This is a new kind of uncertainty. It is not "how big is this thing" but "will this recur". Data statistics say nothing about it. The information lives in the query log, and the automatic materialized-view recommenders in modern warehouses are exactly a loop that mines the workload to make this decision.
The full spectrum is one mechanism with different lengths of commitment:
- Session-scoped subplan memoization: k observed within one session; the commitment ends with it.
- Automatically recommended and maintained materialized views: k predicted from the log; the commitment is reviewed continuously.
- Manually defined materialized views: k asserted by a person; the commitment has no end.
So the deciding input is recurrence probability, not compute cost, and recurrence is a property of people, not of data. This is the only place here where the optimizer needs information about its users. It explains why this item behaves differently from everything around it, and why a different team often owns it.
In one sentence: cross-query sharing has the same break-even as within-query sharing but an unknown k, so it needs workload prediction and not data statistics, and the whole materialized-view spectrum is one mechanism that differs only in length of commitment.