What is Adaptive Query Execution?
Learn how adaptive query execution revises a plan mid-run using runtime stats to fix bad estimates and handle data skew.
Expected Interview Answer
Adaptive query execution is the ability of a query engine to change its execution plan mid-run based on actual runtime statistics observed during execution, rather than committing permanently to the single plan chosen before the query started.
A traditional optimizer picks one plan upfront from estimated statistics and runs it to completion even if the estimates turn out to be badly wrong. Adaptive execution engines instead insert checkpoints, commonly after a shuffle stage in distributed systems like Spark SQL, where they inspect the actual row counts and data sizes produced so far and can switch join strategies (for example from a sort-merge join to a broadcast join), coalesce or split partitions, or re-optimize skewed joins on the fly. This closes the gap between estimated and actual selectivity that plagues purely cost-based planning, especially for complex queries where estimation errors compound across many joins.
- Corrects bad cardinality estimates using real runtime data
- Automatically handles data skew without manual tuning
- Reduces the need for static hints on volatile datasets
- Improves performance for long, multi-stage distributed queries
AI Mentor Explanation
A captain sets a fielding plan before the innings based on the batter’s known tendencies, but after watching the first few overs actually bowled, the captain repositions fielders based on what the batter is really doing today, not the pre-match assumption. This mid-innings adjustment uses real, observed data instead of sticking rigidly to the original plan. Adaptive query execution works the same way, revising its plan mid-run once actual data volumes are observed rather than trusting only the pre-run estimate.
Step-by-Step Explanation
Step 1
Start with an initial plan
The optimizer picks a plan upfront using pre-run cost estimates, same as traditional optimization.
Step 2
Insert runtime checkpoints
The engine pauses at natural boundaries, such as after a shuffle stage, to collect actual row and size statistics.
Step 3
Compare actual vs estimated
If observed data diverges significantly from the pre-run estimate, the engine flags the remaining plan for reoptimization.
Step 4
Switch strategy mid-run
The engine may change join type, repartition skewed data, or coalesce partitions before continuing execution.
What Interviewer Expects
- Clear distinction from static, purely cost-based optimization
- Concrete examples like switching join strategy or handling skew mid-run
- Awareness of where checkpoints occur, such as after a shuffle boundary
- Recognition that this is common in distributed engines like Spark SQL
Common Mistakes
- Confusing adaptive execution with simply re-running EXPLAIN before a query
- Not knowing it operates at runtime checkpoints, not before the query starts
- Assuming it eliminates the need for good statistics entirely
- Failing to mention skew handling as a primary use case
Best Answer (HR Friendly)
“Adaptive query execution means the database engine can change its plan while a query is still running, based on the actual data it sees, instead of being stuck with whatever plan it guessed at the start. This is especially useful for fixing bad estimates and handling skewed data automatically, which traditional static optimization cannot do once execution has begun.”
Code Example
-- Enable adaptive query execution and skew join handling
SET spark.sql.adaptive.enabled = true;
SET spark.sql.adaptive.skewJoin.enabled = true;
-- A join whose plan may switch from sort-merge to broadcast
-- once actual post-shuffle partition sizes are observed
SELECT o.order_id, c.customer_name
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01';Follow-up Questions
- How does adaptive execution differ from traditional cost-based optimization?
- What is a shuffle stage and why is it a natural adaptation checkpoint?
- How does adaptive execution specifically handle skewed join keys?
- What overhead does collecting runtime statistics add to a query?
MCQ Practice
1. Adaptive query execution primarily differs from traditional optimization by doing what?
Traditional optimizers commit to one plan before running; adaptive execution can change the plan mid-run using real runtime data.
2. A common trigger point for adaptive re-optimization in distributed engines is:
Shuffle boundaries expose real, measured data sizes, making them a natural checkpoint to reconsider join strategy or partitioning.
3. Which problem is adaptive query execution particularly effective at solving?
Because it observes actual data at runtime, adaptive execution can detect and rebalance skewed joins that pre-run estimates missed.
Flash Cards
What is adaptive query execution? — The ability to change a query’s execution plan mid-run based on actual observed runtime statistics.
When does re-optimization typically happen? — At runtime checkpoints, such as after a shuffle stage in distributed engines.
What join issue does it commonly fix? — Skewed join keys that static, pre-run estimates underestimated.
How does it differ from static cost-based optimization? — Static optimization commits to one plan upfront; adaptive execution can switch strategy mid-execution.