Repository navigation
rewrite: a cached program still optimizes each shared CTE's placeholder (15% of a cached GPT step) #184
Description
🌀 Claude (xarray-sql ML demos)
Summary
With ddxdb 0.3.3, a cached call of sql_all (one whose program Programs already holds) still calls plan_sql once for each shared CTE's placeholder, which #168 creates by planning the CTE's piece with LIMIT 0. plan_sql optimizes and serializes the whole piece. For demo 2's GPT (xarray-sql's ML benchmarks, xqlsystems/xarray-sql#269), the piece reads the model's view, so this costs 0.15–0.17 s of a 1.0 s cached training step (about 15%), only to learn the placeholder's schema.
Where it shows
The GPT's loss reads its model's view once, into a CTE it reads twice:
WITH predicted AS (SELECT seq, pos, tok, val FROM logits), -- logits: a view
...One SGD step is 11 grad statements, one per parameter table, through ddxdb.ad.sql_all. I timed each DataFusionBackend method from Python, on DataFusion 54.0.0 and an Intel i7-8700, with the machine quiet:
| call | first step | each cached step |
|---|---|---|
plan_sql |
3 calls, 717 ms | 1 call, 143–166 ms |
materialize (68 steps) |
695 ms | 701–707 ms |
view_sql |
58 ms | 53–56 ms |
returns_rows (11 checks) |
53 ms | 47–49 ms |
plan_key |
17 ms | 18–20 ms |
total rewrite + run |
1.64 s | 1.00–1.01 s |
The one plan_sql call on a cached step is the placeholder's:
SELECT * FROM (SELECT * FROM (SELECT seq, pos, tok, val FROM logits) AS __ddx_cte) AS __ddx_empty LIMIT 0Why it costs this much
DataFusion inlines the view's CTEs at every read, so the plan of logits holds hundreds of aggregates, and its optimizer rewrites all of them before LIMIT 0 makes the plan empty. For comparison, ctx.sql(loss).logical_plan() (the unoptimized plan of the whole loss) takes 49 ms, and optimized_logical_plan() 1.6 s.
Possible fix
The placeholder needs only the piece's schema, which the unoptimized logical plan already has. A Backend could register an empty table from that schema (on DataFusion, ctx.sql(piece).schema(), with no optimization), or rewrite could skip the placeholder's plan_sql on a cache hit when plan_key alone decides the hit. Either way, a cached call's cost wouldn't depend on the size of the model's plan.
What's left in a cached step
For #158's ranking: the other 0.70 s is materialize of 68 steps, about 10 ms each, over tables of at most 1,088 rows. Consuming the Substrait plan costs 39 ms in total and registering the results 10 ms, so the rest is DataFusion planning and executing each small step: #157's territory, or fewer and larger steps.
Activity
alxmrs commentedon Oct 31, 2026
Re-measured on main (2a17442 plus a one-line
to_sqlfix), with ddxdb built in release, on the same GPT setup (xarray-sql demo 2, one partition, i7-8700, load ~2):sql_allstepmaterializeview_sqlplan_sql(the placeholders')returns_rowsselect_allplan_keyad.preparegives a step of 280 ms (median of 10), and calls none of these butmaterialize.So the placeholders now cost about 2.6% of a cached
sql_allstep, not 15%, and nothing in a prepared one, which is what a training loop should use. The issue measured ddxdb 0.3.3 on DataFusion 54; I haven't traced which change since then made the placeholders cheap. What's left in a step is DataFusion planning and running each step (materialize). That's #157's (concurrent steps) and einfold's territory, not this issue's. I'd close this one.alxmrs commentedon Oct 31, 2026
🌀 Claude: Re-measured with the coming release, 959fb0c (ddx-ad 0.3.3 and ddx-datafusion 0.4.3, with ddxdb built from it in release). The setup is demo 2's GPT in xarray-sql (xqlsystems/xarray-sql#269), cached
sql_allSGD steps, on DataFusion 55.0.0 and an Intel i7-8700:plan_sql, per cached stepprogram_of_splitstill plans each placeholder'sLIMIT 0piece on every call. But that now costs about 1 ms a call, and it no longer grows with the model's plan. At 2 layers, the loss's plan is much larger and the placeholders cost the same, where with ddxdb 0.3.3 they took 143–166 ms at 1 layer. So the cost this issue measured is gone, and I'm closing it.The 2-layer row shows a different cost: 6.3 s of the 6.9 s goes to checking whether the model's view has changed (
view_sql, then_same_plans). That's now #248.