Deep RL Query Optimizer

QueryOptimizerSelf-Tuning

Beta Works · surface still evolving · works, surface still evolving

What it is

Traditional cost-based optimisers compute a “cost” for each candidate plan from histograms and a fixed formula. The formula is approximate, the histograms drift, and the optimiser keeps picking the same wrong shape until someone files a ticket.

AetheriusDB pairs a deterministic rule-based optimiser (for correctness and warm-up) with a reinforcement-learning agent that learns from observed execution. Each time a query runs, the agent logs the plan shape and the measured latency as a reward. Over time it learns things like “this join order is fast when the tenant has fewer than 100 rows on the left, but slow otherwise” — knowledge no single statistic could encode.

Maturity

The RL optimizer is Beta: it works, but the surface is still evolving. The deterministic rule-based optimiser always runs underneath and is what you fall back to — so you are never dependent on the learned model for a correct plan.

Why it matters

The RL agent improves plans the cost-based system gets persistently wrong — especially for skewed distributions, correlated predicates, or shifting tenant cardinalities. It also gives you a built-in feedback loop: queries you run often tend to get faster on their own.

• Learns from actual latency — the reward is wall-clock time, not an estimate.
• Safe by construction — the rule-based fallback always works, even before the model is trained.
• Self-tuning — repeated queries can improve over hours and days without DBA intervention.

flowchart LR
  classDef plan fill:#1f2640,stroke:#7c5cff,color:#e8eaf0
  classDef exec fill:#1f2640,stroke:#00d4ff,color:#e8eaf0
  classDef rl   fill:#1f2640,stroke:#4ade80,color:#4ade80

  Q[SQL]:::plan --> P[Plan candidates]:::plan
  P --> A[RL agent<br/>picks shape]:::rl
  A --> E[Compiled execution]:::exec
  E --> R[Measured latency<br/>= reward]:::rl
  R -.train.-> A
  E --> OUT[Result]:::exec

How you use it

You can inspect what the optimiser picked, and pin the rule-based plan for a session when you need fully deterministic shapes.

-- See which plan the optimiser chose and why.
EXPLAIN (RL) SELECT o.id, c.name
FROM   orders o
JOIN   customers c ON c.id = o.customer_id
WHERE  o.tenant_id = 42;

-- Use the rule-based plan only, for this session:
SET optimizer = 'rule_based';

-- Inspect the model's accumulated knowledge for a query shape:
SHOW PLAN HISTORY FOR 'orders_join_customers';

During the first few runs of an entirely new query shape, the agent explores — occasionally trying a non-greedy plan to learn its real cost. Once it has confidence, it sticks with the best-performing shape.

Key concepts

Term	What it means
Reward signal	Real measured latency from each execution, used to update the model.
Exploration vs exploitation	The agent occasionally tries an alternative plan to learn its cost, and exploits the best-known shape otherwise.
Rule-based fallback	A deterministic optimiser that always works and is used until the model has enough samples.
Plan history	The per-shape log of plans tried and latencies observed, via SHOW PLAN HISTORY.

Notes & caveats

Tip

In benchmark or regression environments where you need fully deterministic plans, set optimizer = 'rule_based' at the session level. Your benchmarks then measure the executor, not the model’s exploration phase.

Heads up

The model is per-cluster, not per-tenant. If one noisy-neighbour workload distorts the reward signal heavily, plans for unrelated workloads can briefly suffer. Resource-isolation tunables (worker pools, query priorities) are the right tool to keep noisy neighbours from polluting the model.

Adaptive Query Execution →

The runtime safety net that catches mistakes before the model learns.

Whole-Query JIT Compilation →

Turns the chosen plan into a native kernel.

Native Multidimensional Pivot →

Hot rotations the optimiser learns to materialise.

Configuration & Availability →

Feature maturity and defaults.