A reporting query reads from a view that itself reads from three other views, and it is far slower than expected. What problems are typical of deep view-on-view chains, and how would you attack this?

## Why depth is different from complexity A single complex view is planned as one query and is usually fine. Depth is worse for a specific reason: each layer is designed without knowledge of its callers. Layer two joins in a dimension table because one report needed a label. Layer three adds a DISTINCT to protect against duplicates layer two might produce. Layer four filters. Every consumer pays for every decision made below it, and nobody can see the total from the SQL they wrote. ## The failure modes **Work nobody needs.** The classic symptom is a query selecting three columns whose plan touches nine tables. The optimizer can sometimes rescue this: projection pruning skips computing unselected expressions, and join elimination can remove a join entirely when no column from that table is selected and a foreign key to a unique key proves the join changes neither row count nor content. That proof requires the constraints to be declared. Systems that dropped foreign keys "for write performance" lose join elimination and pay for it on every read through the view stack. **Fences.** An aggregation, DISTINCT, window function, LIMIT or set operation anywhere in the chain generally prevents predicates from moving below it. The user's WHERE clause then applies to the output of that block rather than to a base-table scan, so the block is computed in full. A one-row report can cost a full aggregation of the fact table. This is the single most common cause of the "why is this simple query slow" question, and it is completely invisible at the call site. **Estimation collapse.** Selectivity estimates are reasonably good on base-table predicates and degrade through joins, aggregation and DISTINCT. Several layers in, the estimate feeding the top join can be wrong by orders of magnitude. Wrong estimates produce wrong join methods and orders — a nested loop chosen for an expected 10 rows that turns out to be 10 million — and that, not raw row count, is what turns a slow query into a stalled one. **Planning cost and search cutoff.** Expansion is recursive, so joined-relation count grows with depth. Optimizer search space grows steeply with that count, and past an engine-specific threshold most planners abandon exhaustive search for a greedy or genetic method. The consequence is not just slower planning but a plan chosen without full consideration. **Duplicate-row whack-a-mole.** Deep stacks often acquire DISTINCT at intermediate levels to hide fan-out introduced lower down. Each DISTINCT is both a correctness band-aid and a new fence, so the structure gets slower as it gets more defensive. ## How to attack it **Start from the plan, not the text.** Get the execution plan with actual row counts for the failing query. Three things to look for: where the user predicate is actually applied (a late Filter above an aggregate is the smoking gun for a fence); which scans return vastly more rows than the final result; and where estimated and actual rows diverge sharply, which localises the estimation failure. **Map the stack.** Write down each layer's definition and what it adds. Usually one or two layers explain most of the cost, and often a layer exists for a consumer that no longer exists. **Flatten for the hot consumer.** The most effective fix is usually to give the expensive report its own query — written against base tables or one shallow view — expressing exactly what it needs. This trades reuse for predictability. It is the right trade for a small number of hot paths; keep the stack for the long tail of ad-hoc consumers. **Move the fence.** If an aggregation must exist, try to arrange that the filter can be expressed on a grouping key, so the engine can push it through as group elimination. Sometimes restructuring the view to group by the column consumers filter on is enough. **Precompute deliberately.** If an inner aggregate is genuinely shared and expensive, computing it once on a schedule and reading the stored result is a different trade — staleness for cost — and should be chosen explicitly rather than arrived at by accident. **Restore the constraints.** Declared foreign keys and unique constraints are what let the optimizer eliminate joins and estimate better. In a view-heavy system they are a performance feature, not just an integrity feature. **Govern depth.** Set a limit on nesting for shared views, require a named owner and consumer list for each, and treat adding a join or a DISTINCT to a shared view as a change with blast radius across every consumer. ## Interview framing Name the three mechanisms (accumulated work, fences blocking pushdown, estimation and planning degradation), say you would read the plan with actual rows to find which one is dominant, then give the fix ladder: flatten the hot consumer, move or remove the fence, restore constraints, precompute only if genuinely shared. Mentioning the governance angle — views are a shared contract with a blast radius — is what makes it a senior answer rather than a tuning anecdote.