RooConf · 30-minute talk
No Stats?
No Problem.
Building feedback-driven optimizers for lakehouses
Infer what you can.
Measure only what matters.
Learn from what actually ran.
lakehouse metadata
row count
present
NDV
?
histogram
-
freshness
stale
correlation
?
optimizer still
has to choose
01
Problem
The Problem: The Optimizer Must Decide Anyway
02
A cost model can only be as good as the cardinalities it receives.
SELECT ...
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN events e ON e.customer_id = c.id
WHERE o.event_date BETWEEN ? AND ?
AND c.account_id = ?;
The optimizer needs:
join order
join algorithm
broadcast vs shuffle
intermediate row counts
But key NDVs are missing or stale.
This talk follows one concrete path:
semantic repair → plan-aware stats collection → LEO feedback → parameter-risk regions
Problem
Lakehouse Metadata Breaks Classical Assumptions
03
Sparse stats
NDV and histograms may not exist for many columns.
Stale stats
Object-store tables change without optimizer-grade freshness.
Coarse stats
Partition/file-level signals miss expression and join fanout.
Skewed params
A few bindings create very different cardinalities.
The failure mode is not one bad estimate. It is a chain reaction through the plan search.
Solution map
A Layered Survival Model
04
Instead of one giant replacement for the optimizer, use small mechanisms that each answer one question.
1
Infer
Can the query itself repair a missing stat?
Equivalence sets
2
Measure
Which missing stats can actually change the plan?
Magic-number sensitivity
3
Learn
What did reality say last time?
LEO feedback
4
Split
When does a recurring template need a different plan?
Parameter-risk regions
Infer
Layer 1: Equivalence Sets Repair Missing NDVs
05
The cheapest useful statistic may already be implied by the SQL.
orders.customer_id
customers.id
events.customer_id
o.customer_id = c.id
e.customer_id = c.id
{ orders.customer_id, customers.id, events.customer_id }
If one member has useful NDV, seed the missing members before falling back to defaults.
Design rule: Assume all join key columns belong to the same domain, and assume the smallest table as the dim table with PK
Use query semantics first. Collection and learning should start after cheap semantic repair.
Infer
Implementation: NDV repair in Pre-CBO
06
Equivalence sets turn join predicates into a small metadata repair pass.
for each join predicate A = B:
union(A, B)
for each equivalence_set S:
known = NDVs available in S
if known is non-empty:
assign missing NDV from conservative known value
else:
assign DEFAULT_NDV
Walk joins
Extract table-column pairs from equi-join predicates.
Union columns
Build equivalent table-column sets.
Seed missing NDV
Use known member stats before generic defaults.
Fallback safely
If nothing is known, use MagicConstants.DEFAULT_NDV.
Effect: fewer arbitrary NDV fallbacks before CBO starts exploring plans.
Measure
Layer 2: Magic Number Sensitivity Analysis
07
Do not collect every missing stat. Ask whether the plan is sensitive to it.
Candidate column
Missing or uncertain NDV
Low-NDV world
Force small distinct count
→ replan
High-NDV world
Force large distinct count
→ replan
Compare outputs
Plan shape?
Cost delta?
Join strategy?
Collect
only if
it matters
Magic numbers are probes, not final estimates.
Measure
Implementation: auto-stats becomes plan-aware
08
The stats job is scheduled only after the optimizer proves the column matters.
1
Optimize once
Build the baseline plan using current stats and fallbacks.
2
Identify candidates
Columns with missing/weak NDV that appear in filters or joins.
3
Mutate assumptions
Run low/high selectivity what-if worlds.
4
Replan and compare
Check join order, join algorithm, cost, and plan shape.
5
Schedule selectively
Collect NDV only for essential columns.
Goal: make the next plan less fragile, not make the whole catalog complete.
Learn
Layer 3: LEO closes the feedback loop
09
Some cardinality errors only become visible at execution time.
Plan query
Canonicalize subtrees
lookup learned stats
Execute
Physical operators emit
explain/analyze row counts
Process feedback
LEOTask maps runtime stats
back to canonical hashes
Persist learned stats
Cache + storage
confidence metadata
LEO is not a replacement for CBO. It is runtime evidence feeding back into CBO.
Learn
Implementation architecture: where LEO hooks in
10
The implementation touches planning, execution feedback, storage, and metadata providers.
Optimizer
Runtime Engine
Background LEO
Pre-CBO hooks
equivalence sets
query signature
Metadata lookup
row count / cost provider
checks learned stats
Explain/analyze
runtime rowCountIn
runtime rowCountOut
Queue completed plans
logical plan + physical metrics
Canonical mapping
subtree hash
query-context blending
Stats store
Caffeine cache
flush buffer
object/storage tier
Planning uses feedback only when it passes guardrails.
Learn
Canonicalization is the generalization knob
11
Too exact: no reuse. Too broad: whale accounts poison the common case.
Exact
keep literals
Parameterized
erase literals
Partition-aware
erase partition constants
Context-blended
query signature + subtree
Region-aware
future: learned risk regions
Implemented dimensions include literal handling, lineage-aware expressions, commutative normalization, hashing strategy, and query-context blending.
Learn
Confidence: feedback with brakes
12
Feedback is useful only when the system knows when not to trust it.
x = log(rowCount + 1)
delta = x - μ
zScore = |delta| / sqrt(variance + ε)
μ = μ + α · delta
variance = (1 - α) · (variance + α · delta²)
EWMA in log-space handles multiplicative row-count error.
LOW
zScore > 2.0
ignore learned stat
HIGH
zScore < 0.2
frequency > 2
safe to reuse
MEDIUM
otherwise
use with caution
Guardrail: low confidence or prior failures fall back to normal estimation.
BI workload
BI workloads are where feedback becomes powerful
14
Dashboards repeat SQL shapes. Parameters change.
WHERE account_id = ?
AND region = ?
AND product_line = ?
AND event_date BETWEEN ? AND ?
High repetition
Same template runs many times per day.
Useful feedback
Earlier executions are relevant to later planning.
Parameter skew
Most bindings are normal; a few are extreme.
Tail risk
Wrong plan can dominate p95/p99 latency.
The feedback problem changes from “what happened?” to “for which parameter bindings is this feedback valid?”
BI workload
The plan-cliff pattern
15
Most parameter bindings behave like the common case. A few create different plan economics.
account size / fanout →
date window width
common case
plan P1
whale
plan P2
broad window
plan P3 / robust fallback
Research target
Learn compact regions online, keep a bounded set of useful plans, and reduce tail-latency regret.
Not the goal
One plan per parameter value. That overfits and explodes plan-cache management.
Research
Research direction: online parameter-risk regions
16
Move from learned subtree stats to learned validity regions for recurring templates.
1
Observe
template hash
parameter binding
runtime signals
2
Score risk
q-error
spills
latency regret
plan instability
3
Split / merge
compact regions
bounded management cost
4
Choose plan
generic plan
region plan
robust fallback
5
Retire / drift
expire stale regions
reactivate if risk returns
Key question: when is a parameter value an outlier, and when is it a new optimizer-relevant region?
Benefits
What changes in optimizer behavior
18
The goal is robustness under incomplete statistics, not perfect statistics.
Layer
Optimizer behavior
Practical benefit
Before
All unknown NDVs look alike
Generic fallbacks and brittle join order
After equivalence sets
Join semantics repair missing NDVs
Less arbitrary cardinality input
After sensitivity analysis
Stats collection is plan-aware
Lower metadata cost for the same planning benefit
After LEO
Execution feedback corrects repeated errors
Improves repeated templates and common subtrees
With parameter regions
Feedback is scoped to valid bindings
Less tail-latency regret from plan cliffs
Takeaways
19
When statistics are missing, the optimizer should not guess harder. It should ask better questions.
Equivalence sets
Does the query imply a missing NDV?
Sensitivity analysis
Would collecting this stat change the plan?
LEO
What did previous executions prove?
Canonicalization + confidence
When is feedback reusable?
Parameter regions
When does the same template need a different plan?
The goal is not perfect lakehouse statistics. The goal is useful plans when statistics are incomplete by default.