0

Sql Optim

Fresh

An **execution-grounded** environment for training agents to write fast SQL. The agent is given a slow query plus its schema and must return a rewrite.

Type
RL Env
License
bsd-3-clause
Published
Jul 2026

Cite

Notes

Only stored in your browser.

SQL Query Optimization Environment

An execution-grounded environment for training agents to write fast SQL. The agent is given a slow query plus its schema and must return a rewrite. Reward is not a keyword heuristic: the environment runs both the original and the optimized query against a real temporary-file-backed DuckDB database seeded with realistic synthetic data (10k users, 500k orders, 1k products, 1M events), then scores the agent on measured speedup and result-correctness.

Five tasks scale from basic anti-patterns to expert window-function audits.

Quick Start

from sql_optim_env import SQLOptimAction, SQLOptimEnv

with SQLOptimEnv(base_url="http://localhost:8000").sync() as env:
    obs = env.reset(task_id="task_1_basic_antipatterns").observation
    print(obs.sql_query)          # the slow query to fix
    print(obs.schema_info)        # tables, row counts

    result = env.step(
        SQLOptimAction(
            suggestions=[
                {"issue_type": "select_star", "line": 1,
                 "description": "SELECT * reads every column", "severity": "high",
                 "fix": "project only the needed columns"},
            ],
            optimized_query="SELECT id FROM orders WHERE customer_id = 5000",
            summary="Dropped SELECT * and the non-sargable CAST.",
            estimated_improvement="~10x faster",
            approved=False,
        )
    )
    print(result.reward, result.done)
    print(result.metadata["reward_breakdown"])
    print(result.metadata["execution"]["speedup"])   # real DuckDB speedup

Async usage mirrors the other environments:

async with SQLOptimEnv(base_url="http://localhost:8000") as env:
    result = await env.reset(task_id="task_1_basic_antipatterns")

Action

FieldTypeMeaning
suggestionslist[dict]Issues found: issue_type, line, description, severity, fix
optimized_querystrThe rewritten SQL — executed for real
summarystrOverall analysis
estimated_improvementstrAgent's own speedup estimate
approvedboolTrue if the query is already optimal

Observation

sql_query, schema_info, task_description, difficulty, step_count, max_steps, issues_found_so_far, and last_execution (the previous step's real timing comparison, so the agent can refine its rewrite). The step reward is on reward; the composite reward_breakdown, feedback, and full execution comparison are under metadata.

Reward

Composite score in [0, 1], computed inside the environment:

ComponentWeightSource
Execution speedup35%real DuckDB timing ratio
Result correctness20%do both queries return identical rows?
Issue detection25%flagged issues vs. ground truth
Approval correctness8%correctly judged the query bad/good
Summary quality7%thoroughness of the written analysis
Severity labels5%severities present

Tasks

Five tasks (task_1_basic_antipatterns … task_5_*) covering SELECT *, non-sargable predicates, functions on filter columns, join ordering, and window functions, spanning easy → expert.

Run the server locally

uvicorn server.app:app --host 0.0.0.0 --port 8000

Resource budgets

openenv.yaml declares 1024 MiB memory and 1024 MiB writable storage for Docker validation. DuckDB uses a 256 MiB memory_limit and a 128 MiB max_temp_directory_size, for both dataset construction and query execution. Explicit MiB units avoid confusion with DuckDB's decimal GB.

The docker-local validation provider enforces the memory limit with swap disabled and splits writable storage between /tmp tmpfs and /dev/shm (up to 64 MiB). The temporary database and spill files in /tmp therefore also consume container memory. A regression checks the real seeded database, spill allowance and shared-memory allowance plus DuckDB's memory cap fit in half the declared memory budget, leaving at least 512 MiB for runtime overhead. This is budget headroom, not a guarantee that every agent query fits.

DuckDB's memory limit covers its buffer manager, not the Python heap or every DuckDB allocation. Its spill cap excludes the base database. Agent query results never enter Python, where neither the memory limit nor the query deadline applies: rows are only counted, timed and compared inside DuckDB. Allocations DuckDB does not track, such as one huge repeat(), are bounded only by the container, which may kill the server with an out-of-memory error. The Dockerfile and a direct uvicorn launch do not enforce the YAML budgets themselves: deployments must configure their runtime limits explicitly.

Tests

PYTHONPATH=src:envs uv run pytest tests/envs/test_sql_optim_environment.py -v