Query Optimizer

Optimizer Pipeline

Every SQL query flows through four stages:

Cost-Based Optimisation

The optimizer estimates the cost of each plan candidate based on:

• Selectivity estimation: histogram-based for range predicates, HyperLogLog for distinct counts
• Index selection: B+Tree for range scans, Hash for equality, BRIN for monotonic columns, GIN for text/arrays
• Join ordering: dynamic programming for ≤ 8 tables, greedy heuristic beyond
• Join method: nested loop (small RHS), hash join (equi-join), merge join (pre-sorted)
• Parallel decision: parallelise if estimated rows > 10,000 (configurable via parallel_min_rows)

Adaptive Re-Optimisation

If the optimizer's cardinality estimate diverges significantly from actual rows during execution, Absolute DB can switch plans mid-query:

• Monitor actual vs estimated row counts at each operator
• If actual exceeds estimate by > 10×, re-plan the remaining subtree
• Common switch: sequential scan → index scan (or vice versa) when estimates are wrong

Parallel Query

EXPLAIN

-- Text output
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Analyse (actually execute and show actual row counts)
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

-- Visual SVG tree in the web console
-- Navigate to Console > EXPLAIN panel, paste query, click "Explain"

SIMD & JIT Acceleration

The optimizer can route qualifying expressions through two acceleration paths:

• SIMD vectorisation: column scans processed 8–16 values at a time via AVX2/AVX-512/NEON
• JIT compilation: predicate expressions compiled to native machine code (x86-64 + ARM64) at query time

Both are transparent to the user and activated automatically by the optimizer when beneficial.

Related Documentation