Adaptive Query Execution

QueryPlannerAdaptive

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

What it is

Cost-based optimisers pick a plan from estimates: “this join will produce about a billion rows, so use a hash shuffle.” When the estimate is wrong, classical systems run the bad plan to completion anyway — sometimes for hours.

AetheriusDB places checkpoints inside long-running plans. At each checkpoint it compares the real row counts against the estimate. When reality diverges enough, the executor pauses, asks the planner for a better physical shape, swaps the affected part of the plan in place, and resumes. The client sees one query returning the right answer — just faster than the original plan would have managed.

Why it matters

Adaptive execution turns “the planner was wrong” from a tail-latency catastrophe into a self-healing event. You get the predictability of a rule-based optimiser when stats are good and a safety net when they aren’t.

Strategy switching mid-flight — a hash join becomes a broadcast join when the left side turns out to be tiny.
Shared scans — multiple queries reading the same big table can merge into one read stream.
No client retries — pivots happen below the query surface; the result set is uninterrupted.

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

  A[Initial plan:<br/>Hash join<br/>est 1B rows]:::plan
  A --> B[Checkpoint:<br/>actual = 50 rows]:::obs
  B --> C[Switch to<br/>Broadcast join]:::new
  C --> D[Streamed result]:::new

How you use it

There is no syntax to enable adaptive execution — every long query is eligible. You can see when a pivot happened by reading the runtime explain output.

-- A join whose left side may be much smaller than the planner thinks.
SELECT o.id, c.name
FROM   orders o
JOIN   customers c ON c.id = o.customer_id
WHERE  o.placed_at > NOW() - INTERVAL '5 minutes';

-- After it runs, ask what actually happened:
EXPLAIN (ANALYZE, ADAPTATIONS)
  SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id;
-- adaptations:
--   t+12ms : hash-join → broadcast-join (left card 50 vs est 1.2B)

You can disable adaptive pivots for a session when you need fully deterministic plan shapes — for example during regression testing — but for production traffic you almost always want the safety net on.

Key concepts

Term	What it means
Checkpoint	A point in the plan where the executor compares estimates to reality.
Strategy pivot	An in-place swap from one physical operator (hash join) to another (broadcast or sort-merge).
Shared scan	A read stream that lets a new query attach mid-scan and pick up rows from the current cursor.
Adaptation log	A per-query record of every pivot, visible via `EXPLAIN ANALYZE`.

Autonomous indexing

Adaptation also works across queries, not just within one. The engine watches the predicate and grouping shapes it sees and, when a missing-index pattern keeps paying off, it creates a real physical index on its own — a plain B-Tree named eif_auto_*. It is deliberately conservative: it checks only periodically, creates at most a couple of indexes per check, never touches system tables, never creates unique or partial indexes, and is gated by a configuration flag. Because the index is catalog-persisted, the behaviour is self-healing — a workload that lost an auto-index simply causes it to be recreated. See Advanced Indexing for the indexes you declare explicitly, which remain the predictable, supported surface.

Notes & caveats

Deep RL Query Optimizer →

Learns from every pivot to improve first-shot plans.

Whole-Query JIT Compilation →

How an in-place plan swap is realised as a recompiled kernel.

Approximate Query Processing →

Another way to avoid being burned by mis-estimates.

Configuration & Availability →

Feature maturity and defaults.