A nightly reporting statement joins around 25 relations. It spends longer being planned than executing, and its chosen plan differs between runs. How would you approach this?

## Reading the symptoms Two observations arrive together and they are diagnostic. **Planning longer than execution.** Exhaustive join-order search is exponential (`3^n` combinations, `2^n` memo entries). At 25 relations that is astronomically out of reach, so the engine has already given up on exhaustive search — but even the heuristic search over a graph that wide, together with access-path and operator enumeration per node, is expensive. If planning dominates, the query is either being replanned on every execution (no plan reuse) or the search budget is set far too generously. **Unstable plans between runs.** Deterministic search cannot do this unless its inputs changed. So either the statistics changed underneath (auto-refresh fired between runs), or the optimizer is using a randomized/genetic search whose outcome depends on a seed. Both are worth distinguishing before acting, because the fixes differ. ## Step 1 — measure before touching anything Separate planning time from execution time for a cold plan and a warm one. Record the plan shape per run and correlate with runtime. You want to know: is the variance costing real time, or are the different plans all roughly equal? A query whose plan wobbles between three plans that all run in 40 seconds is a cosmetic problem; one that wobbles between 40 seconds and 40 minutes is an availability problem. ## Step 2 — shrink the optimization region This is the highest-leverage move, because it changes the exponent. - **Decompose the statement.** Compute a stable, well-understood intermediate — the reduced fact set, or the resolved dimension keys — into a temporary or intermediate table, then join against it. Two 12-relation problems are not half of a 25-relation problem; they are exponentially smaller. - **Find hidden relations.** Layered views, inlined subqueries and table functions flatten into the same optimization region. A statement that mentions six names can present twenty-five base relations to the optimizer. Look at the flattened relation count, not the text. - **Remove joins that contribute nothing.** Wide reporting statements accumulate joins whose only purpose is a column that is no longer selected, or an existence check better expressed as a semi-join. Every relation removed cuts the space super-linearly. ## Step 3 — pay for planning once A nightly report should not be replanned per execution. Options, roughly in order of preference: - Prepared statements / server-side plan cache, so repeated executions reuse a plan. - An engine-level facility for storing and reusing a validated plan for a specific statement, which also freezes the shape and removes the run-to-run variance. - As a last resort, optimizer directives that constrain the join order. These are debt: they must be revisited when data volumes shift, and they silently prevent the optimizer from adapting. Freezing a plan is a real decision, not a hack — for a nightly batch job, predictable 12 minutes usually beats an average of 9 minutes with an occasional 90. ## Step 4 — fix the inputs before buying more search Once the search is heuristic, its quality is dominated by the cardinality estimates it is scoring candidates with. Estimation error compounds multiplicatively up a deep join tree: a 3x underestimate at each of five levels is a 243x error at the top, which is more than enough to make the search prefer a catastrophic order. Refresh statistics, look for the obviously wrong estimates by comparing estimated to actual row counts at each level, and address correlated predicates. This is nearly always worth more than extra search budget. ## Step 5 — only now, tune the search itself Raising the exhaustive-search threshold can be right for a long-running batch statement: two extra seconds of planning against twelve minutes of execution is a fine trade, and exact search may find a materially better order. Measure it — raise the threshold, record planning time and total runtime, and keep the change only if it pays. For randomized search, fixing the seed restores determinism at the cost of possibly freezing in a mediocre plan. ## Step 6 — question the shape of the work A 25-way join in a transactional schema is often a sign the workload is analytic and is being served by a layout designed for transactional writes. The right long-term answer may be a different physical layout or a purpose-built reporting store — a modelling decision rather than an optimizer decision. Raise it explicitly rather than tuning forever. ## The framing that lands in an interview Say plainly: *the optimizer's job is a search under a time budget, and at 25 relations the budget has bound. My levers are to make the problem smaller, to pay for planning once, and to improve the estimates the search is steering by — in that order. Tuning the search itself is last, and freezing the plan is a legitimate answer for a batch job.*