docs/design.md

Design: execution-grounded reward

Goal

Train or evaluate LLMs on SQL optimization using feedback that reflects what actually happens when a rewritten query runs on realistic data—not lexical overlap with a rubric alone.

Why this reward is hard to game

1. Speedup (35%) comes from median wall-clock over multiple DuckDB runs of both the original and the candidate rewrite. You cannot claim a 10× improvement without the engine measuring roughly that ratio on this dataset.
2. Correctness (20%) uses sorted row comparison for modest result sets, and a order-independent checksum (or count fallback) for large sets so parallel / non-deterministic ordering does not false-negative legitimate rewrites.
3. Issue detection (25%) still uses keyword overlap against declared ground-truth issue types. That piece is gameable in isolation—which is why it is capped and combined with execution signals. A model that only “talks” about fixes without a faster, correct query cannot max the score.

Together, “fast + wrong” loses the correctness mass; “verbose + slow” loses the speedup mass; “keywords only + empty SQL” loses both execution components.

Observation loop

Each step returns an Observation that may include last_execution from the previous graded action (timing, speedup, results_match, verdict). The grader always re-executes when scoring a new action; last_execution is for agent iteration and demos, not a cached substitute for grading. Stripping last_execution from the prompt is an observation-space ablation for the LLM only; it does not change grade() (see scripts/ablation.py for reward-component ablations).

Edge cases and limitations

Topic	Behavior
Timeouts / huge latency	Failed execution or extreme median times yield low or zero speedup credit; errors surface in feedback.
Semantic rewrites that change row counts	If the optimized query returns different rows, correctness is partial or zero even if the SQL is “clever.” Some tasks intentionally trade strict row identity for performance; the grader reflects that honestly.
DuckDB version differences	Executor prefers portable checksum patterns and falls back to count-only if needed.
Single-agent design	There is no second “oversight” LLM in the environment contract. The database is the ground-truth critic. Adding a critic model would be analysis-only unless the action space changes.
Keyword detection	Known limitation: suggestions should align with tasks.py ground-truth keywords for full detection credit.

Threat model (reward hacking)

• Copying the original query as “optimized” → speedup ≈ 1×, low speedup score; may still get issue/summary points.
• Returning empty optimized_query → no execution credit; very low total.
• Wrong but fast query → results_match false → at most partial correctness, capped total.

For systematic sensitivity analysis, run scripts/ablation.py with component masks (see graders.py GradeMask).