QueryPilot (Adaptive Join Engine)

Type what you need in plain English — QueryPilot picks the optimal join algorithm, explains why, and switches strategy mid-execution if its estimates turn out wrong.

30-second quickstart

QueryPilot requires Python 3.10 or newer.

python -m venv .venv
source .venv/bin/activate
pip install -e .

# Optional: QueryPilot runs fully offline without this.
export GEMINI_API_KEY="your-key"

# Optional: model availability and free-tier quotas change over time.
export GEMINI_MODEL="gemini-3.5-flash-lite"

python demo_data/generate.py
python -m querypilot \
  "join demo_data/customers.csv and demo_data/orders.csv on customer_id, low memory"

Real output from the generated demo data, using the offline parser:

Parser: offline-regex
Chosen algorithm: hash_join
Reason: Selected hash_join because the smaller input fits within the 128.000 MB memory budget while optimizing for latency.
Adaptive switch: no
Switch reason: none
Rows joined: 50000
Elapsed_ms: 7.269

Try two other planner outcomes:

# Already-sorted inputs select sort-merge.
python -m querypilot \
  "join demo_data/events_a.csv and demo_data/events_b.csv on event_id, fast"

# A build side larger than the budget selects sort-merge.
python -m querypilot \
  "join demo_data/customers.csv and demo_data/orders.csv on customer_id, fast, 0.01 mb"

Use --config with a JSON string or JSON file path to bypass natural-language parsing:

python -m querypilot --config \
  '{"file_a":"a.csv","file_b":"b.csv","join_column":"id","memory_budget_mb":256,"optimize_for":"throughput"}'

Architecture

flowchart LR
    A["Natural-language request"] --> B{"Parser"}
    B -->|"API available"| C["Gemini"]
    B -->|"No key or API failure"| D["Offline regex fallback"]
    C --> E["TableStats"]
    D --> E
    E --> F["JoinPlanner"]
    F --> G["AdaptiveExecutor"]
    G --> H["Result + explanation"]

Loading

Gemini is optional and imported lazily. CSV execution, statistics, planning, joining, reporting, and the fallback parser all use local Python code.

Planner decision matrix

Rules are evaluated from top to bottom.

Condition	Algorithm	Why
Either table has fewer than 100 rows	nested_loop_join	Avoids hash-table and sorting setup for a tiny input
Both tables are sorted on the join key	sort_merge_join	Exploits ordered inputs and advances through them once
The smaller table exceeds memory_budget_mb	sort_merge_join	Avoids building an oversized in-memory hash table
Otherwise	hash_join	Uses expected linear-time build and probe when the build side fits

Benchmark results

Run python benchmark.py to reproduce this table. Times are median wall-clock milliseconds from the current checkout.

Scenario	Nested loop (ms)	Hash (ms)	Sort-merge (ms)	Planner pick	Fastest	Within 10%?
small×large	0.117	0.758	0.314	nested_loop_join	nested_loop_join	✓
both-sorted	2.061	0.849	0.120	sort_merge_join	sort_merge_join	✓
skewed keys	1.115	0.083	0.119	hash_join	hash_join	✓
memory-constrained	2.384	0.967	0.180	sort_merge_join	sort_merge_join	✓
wrong-cardinality-estimate	0.896	0.058	0.084	hash_join	hash_join	✓

• small×large: Nested loop wins because one outer row makes a single scan cheaper than building a hash table or sorting.
• both-sorted: Sort-merge wins by consuming ordered inputs linearly without hashing the composite-style benchmark keys.
• skewed keys: Hash join wins because bucket lookup avoids repeated scans despite the hot-key distribution.
• memory-constrained: Sort-merge wins because it avoids the disallowed hash build and handles the orderable keys efficiently.
• wrong-cardinality-estimate: Hash join is the raw timing winner, while the adaptive run separately demonstrates a mid-build switch when actual build rows cross the 10× estimate threshold.

How this mirrors SQL Server adaptive joins

SQL Server batch-mode Adaptive Joins defer the hash-versus-nested-loop decision until the build input has been scanned. QueryPilot applies the same runtime-feedback idea: it watches rows and estimated memory while constructing the hash table, then aborts mid-build when the assumptions behind the plan are no longer valid.

The key difference is the alternative chosen. SQL Server compares Hash Join with Nested Loops at a cardinality threshold. QueryPilot falls back from hash to sort-merge when the build side exceeds its row estimate by more than 10× or breaches the memory budget.

The benchmark's wrong-cardinality scenario produces this live switch:

algorithm_started: hash_join
algorithm_finished: sort_merge_join
switched: True
switch_reason: Actual build-side row count exceeded the estimate by more than 10x (101 observed vs 10 estimated).
elapsed_ms: 0.232375

Design decision: the LLM never chooses the algorithm

Gemini only translates user intent into configuration JSON:

{
  "file_a": "customers.csv",
  "file_b": "orders.csv",
  "join_column": "customer_id",
  "memory_budget_mb": 128,
  "optimize_for": "latency"
}

The deterministic planner chooses the physical algorithm. This boundary makes plans reproducible, keeps every decision unit-testable, and prevents an LLM from inventing unsupported operators or hallucinating execution costs. The same request and statistics always produce the same plan, whether Gemini or the offline parser produced the configuration.

Known limitations

• Statistics are sample-based and can miss skew outside the sample.
• The planner has no correlated-column or multi-column distribution awareness.
• Execution is single-node and in-memory; there is no spill-to-disk path.
• The cost model is heuristic rather than calibrated against a storage engine.

Testing

The project has 52 tests grouped into four suites:

Suite	Tests	Coverage
Core joins (test_1_joins.py, test_joins.py)	31	Algorithm equivalence, empty inputs, duplicate keys, no matches, key indexes, and stats
Planner (test_2_planner.py)	10	Sampling, all planner rules, memory fallback, 10× estimate fallback, and result equivalence after switching
Natural language (test_3_nl.py)	7	Gemini JSON, fenced responses, defaults, retry, mocked calls, model override, and offline regex
End to end (test_4_e2e.py)	4	Demo generation, offline CLI, explicit JSON, missing columns, and all benchmark scenarios

Run them with:

pytest

License

MIT. See LICENSE.