Traditional query optimizers make a one-time decision before execution begins. They examine statistics, estimate cardinalities, and produce a plan. That plan runs to completion regardless of whether the estimates were accurate. When statistics are stale, data is skewed, or predicates are correlated in ways the optimizer didn’t anticipate, the original plan can be orders of magnitude slower than necessary.

Adaptive query execution changes this by allowing the database to observe actual runtime data and adjust the plan dynamically. The optimizer starts with an initial plan, but monitors key metrics as execution proceeds. If intermediate result sizes diverge significantly from estimates, the engine can reoptimize on the fly.

Why Static Plans Fail

Query optimizers rely on statistics: histograms, distinct value counts, null percentages, and correlation data. These statistics are collected periodically, not in real time. Between collection cycles, data distribution changes. A table that was uniform last week might now have heavy skew. A filter that used to be selective might now match millions of rows.

Correlated predicates compound the problem. If a query filters on both country='US' and state='California', the optimizer typically assumes independence and multiplies the selectivities. In reality, all California rows are in the US, so the combined filter is far more selective than the estimate suggests. The optimizer might choose a nested loop join based on a low cardinality estimate, then discover at runtime that it’s processing far more rows than expected.

Runtime Reoptimization

Adaptive execution introduces decision points within the query plan. After scanning a build-side table or completing a filter, the engine checks whether the actual row count is close to the estimate. If the divergence exceeds a threshold, it triggers reoptimization.

The most common adaptation is join strategy switching. A plan might start with a hash join, expecting a small build side. If the build side turns out to be large, the engine can switch to a sort-merge join or broadcast the smaller side in a distributed query. Some systems cache multiple candidate plans and choose among them at runtime based on observed cardinalities.

Another adaptation is dynamic partition pruning. In a star schema query, the fact table might be partitioned by date. If a dimension table filter reduces the date range significantly, the engine can prune partitions dynamically rather than scanning the entire fact table. This requires propagating filter results from one branch of the plan to another during execution.

Shuffle and Exchange Decisions

In distributed query engines, adaptive execution also governs data movement. A broadcast join sends one entire table to every node, which is efficient when that table is small. A shuffle join partitions both tables and redistributes rows by join key, which scales better for large inputs.

The optimizer’s initial choice depends on estimated table sizes. If the estimate is wrong, broadcast can overwhelm network and memory. Adaptive execution observes the actual size after scanning and can switch strategies before the join begins. Some systems start with a hybrid approach: begin broadcasting, but abort and switch to shuffle if the data exceeds a threshold.

Statistics Feedback Loops

Some databases extend adaptive execution beyond a single query. After a query completes, the engine compares actual cardinalities to estimates and updates statistics or injects hints for future queries. This creates a feedback loop where the optimizer learns from its mistakes.

This is particularly valuable for parameterized queries. The first execution might use a generic plan based on average statistics. Subsequent executions with different parameter values can use observed distributions to refine the plan. If a query always runs with status='PENDING' and that status is rare, the optimizer learns to prefer an index scan even if the generic plan chose a table scan.

Tradeoffs and Overhead

Reoptimization introduces latency. Recomputing a plan mid-execution takes CPU and memory. The decision to adapt must weigh the cost of reoptimization against the expected benefit of a better plan. If the remaining work is small, sticking with a suboptimal plan may be faster than switching.

Adaptive execution also complicates debugging. A query might perform differently on successive runs not because the data changed, but because the runtime decision points triggered different adaptations. Execution plans become less deterministic, and performance tuning requires understanding both the initial plan and the conditions under which it adapts.

Adoption and Standardization

Adaptive query execution has moved from research into production systems. Spark introduced it in version 3.0, focusing on dynamic partition pruning and skew joins. Databases like SQL Server and Oracle have implemented runtime plan correction for years, though under different names: adaptive joins, cardinality feedback, or reoptimization.

The technique works best in analytical workloads where queries are complex, data is large, and the cost of a bad plan is high. OLTP systems, with simpler queries and stricter latency requirements, gain less from the overhead of runtime adaptation.

As data volumes grow and schema complexity increases, static optimization becomes harder to get right. Adaptive execution shifts some of the burden from pre-execution guesswork to runtime observation, making query performance more robust to estimation errors and evolving data distributions.