A single-process, single-threaded analytical SQL engine written from scratch in C++17. It loads CSV or pipe-delimited tables into a columnar, encoded, zone-mapped store, plans queries through a cost-based optimizer, and executes them with a vectorized, late-materializing operator pipeline. It is a read-only query engine: no writes, no transactions, no persistence beyond the input files, no indexes, no parallelism.
It answers all 22 TPC-H queries. On official dbgen data at SF=1 every answer
matches the published TPC-H answer set. By geometric mean it is 3.0× faster than
SQLite and 1.9× faster than single-core PostgreSQL at SF=1, indistinguishable
from PostgreSQL at its default parallelism, and 16× slower than DuckDB on 14
cores. The cost-based optimizer is worth 2.44× at SF=1.
Supported SQL. SELECT [DISTINCT] ... FROM ... [JOIN | LEFT JOIN ... ON ...]* [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT]. Multi-way and multi-key
equi-joins with residual ON predicates. Aggregates COUNT, SUM, AVG,
MIN, MAX, COUNT(DISTINCT x). Arithmetic with SQL precedence, aliases,
[NOT] BETWEEN, [NOT] LIKE, [NOT] IN, searched CASE, SUBSTRING, date
literals and constant-folded interval arithmetic, IS [NOT] NULL, three-valued
AND/OR. Subqueries in WHERE and HAVING: scalar, [NOT] EXISTS,
[NOT] IN, correlated or not. Derived tables in FROM and JOIN. EXPLAIN
and EXPLAIN ANALYZE with per-node exclusive timings.
Types. INT (64-bit), DOUBLE, STRING, plus SQL NULL throughout. Dates
are ISO-8601 strings compared lexicographically.
Deliberately out. DDL, writes, transactions, indexes, window functions,
CTEs, RIGHT/FULL joins, LATERAL, general prefix NOT, scalar functions
other than SUBSTRING, column ordinals. Each rejected construct produces a named
error rather than a wrong answer. The full list of refusals, with the reason for
each, is in development.md.
A query moves through six stages:
SQL text
│ Lexer + recursive-descent Parser src/parser/
▼
AST
│ Binder — resolves every column to (relation slot, query level)
│ Validator — types, aggregation rules, join-key rules, dialect refusals
▼ src/planner/binder.cc, validator.cc
Logical plan src/planner/logical_plan.*
│ Optimizer passes (vectorized path only)
│ constant folding → subquery rewrites → predicate & projection pushdown
│ → cardinality estimation → join ordering → physical join selection
▼
Physical plan
│ Volcano executor (row-at-a-time) or Vectorized executor (chunk-at-a-time)
▼ src/execution/
Rows
src/common/ Value, Schema, TypeId, date utilities
src/catalog/ catalog.json loader, TableMetadata, TableStats (min/max/NDV/nulls per column)
src/storage/ CSV/.tbl loader, ColumnarTable, RLE + dictionary encoders, zone-map ChunkPruner
src/parser/ Lexer, Parser, AST, expression utilities and the AND-cascade rule
src/planner/ Binder, Validator, logical plan, optimizer passes, cost model, plan builders
src/execution/ Volcano evaluator + vectorized Vec*Node operators, expression executor, Bloom filter
src/cli/ main.cc — argument parsing, loading, result printing
tests/ GoogleTest suites (one binary, swiftql_tests)
python_tools/ data generators, SQLite oracle, regression suite, TPC-H harnesses, figures
benchmarks/ hash-vs-SIMD join crossover calibration
docs/ benchmark reports, raw JSON measurements, figures
Tables load into a ColumnarTable: one typed array per column, split into
8192-row chunks. Each chunk carries min/max metadata (a zone map). String
columns are dictionary-encoded; columns with long runs are run-length encoded
when the run count is below a quarter of the row count. A --storage row
mode keeps the original std::vector<Row> image and exists as the correctness
baseline.
There is exactly one access path: a sequential scan that consults zone maps to skip chunks whose min/max prove no row can match the pushed-down predicate. Column statistics (row count, min, max, distinct count, null count, average width) are computed at load time and feed the optimizer.
The parser is hand-written recursive descent. BETWEEN is desugared to two
comparisons so that pushdown, pruning and range selectivity match one shape.
The binder resolves every ColumnRef to a relation slot (its position in the
enclosing block's range table) and a query level (how many blocks out it
lives). Every later pass reads columns by slot, never by table name, which is
what makes self-joins, aliases, derived tables and correlated subqueries resolve
the same way.
Runs on the vectorized path only. Passes, in order:
| Pass | What it does |
|---|---|
| Constant folding | Folds literal arithmetic, date + interval, and SUBSTRING on constants at plan time |
| Subquery materialization | Uncorrelated scalar and EXISTS bodies run once before planning and become constants |
| Subquery lowering | x [NOT] IN (SELECT ...) becomes a hash semi-join / anti-join |
| Decorrelation | Correlated [NOT] EXISTS becomes a semi/anti join; a correlated scalar aggregate becomes a GROUP BY derived relation left-joined back (the TPC-H Q17 shape) |
| Predicate pushdown | Splits AND conjuncts and pushes each to the lowest relation that can evaluate it; an OR conjunct contributes a weaker single-relation restriction per side |
| Projection pushdown | Narrows every scan to the columns the block references |
| Cardinality estimation | Selectivity from column statistics under the independence assumption; join cardinality from per-key distinct counts |
| Join ordering | Left-deep dynamic programming over the join graph, greedy above 10 relations; the search result is installed only if it costs no more than the written order |
| Physical join selection | Hash join by default; a SIMD loop join (AVX2 or NEON) when the calibrated cost crossover favours it, which after the hash join rewrite is only a single-row build side; Bloom filter pushed from build side into the probe scan for inner and semi joins |
Two executors share one plan shape and one expression evaluator.
| Volcano (row-at-a-time) | Vectorized (chunk-at-a-time) | |
|---|---|---|
--storage row |
supported | not supported |
--storage columnar |
supported | supported (default for benchmarks) |
Volcano is the classic open() / next() / close() iterator model over
Row. It builds exactly one join and runs no optimizer, so it is the
correctness baseline rather than the feature-complete path. Multi-way joins,
subquery joins and derived tables are refused there by name.
Vectorized operators exchange 1024-row DataChunks. Each column is a
ColumnVector with a validity mask. A filter produces a SelectionVector of
surviving row indices instead of copying rows, and selections cascade through
the pipeline until VecProjectNode materializes the result. Expressions are
compiled once per query by ExpressionExecutor and evaluated over whole chunks.
The blocking operators (VecSortNode, VecHashJoinNode,
VecHashAggregateNode) keep column-wise state: the hash join stores its build
side by column with a chained index and emits late-materialized output; the
aggregate serializes group keys straight from the column into a reused buffer;
the sort evaluates keys once per row.
EXPLAIN prints the logical plan, the optimized plan with estimated rows, and
the physical plan with each join's cost decision. EXPLAIN ANALYZE adds actual
rows and exclusive self-time per node.
A per-process result cache keyed on the SQL string returns repeated queries
without execution (--no-cache bypasses it).
- Late materialization everywhere. Filters and joins pass indices, not rows. Rebuilding the three blocking operators to honour this cut total SF=0.1 TPC-H latency by 26%.
- One access path, no indexes. Zone maps are the only pruning. On TPC-H
they prune nothing: every 8192-row chunk of
l_shipdatespans the whole seven-year domain. Sorting at load was measured and rejected because it would only help queries SwiftQL already wins. - Subqueries are rewritten by shape, never by cost. A constant contribution materializes; a membership test becomes a semi-join; a correlated aggregate becomes a grouped derived table. Shapes no rewrite can express are refused with a message that names the shape. There is no dependent-join fallback.
- Join ordering is bounded by the written order. Dynamic programming's optimal-substructure assumption fails when a relation has no statistics, so the search's pick is installed only when it scores at or below the query as written.
- Join algorithm chosen by a measured crossover. The hash-vs-SIMD-loop
constant in the cost model is calibrated on-device by
benchmarks/calibrate_join_crossover.cc(docs/hash-vs-simd-crossover.md). After the hash join was rewritten, recalibration showed it winning at every build size tested, so the loop join is now reachable only for a single-row build side. Before recalibration the stale constant made one query 2.9× slower. ANDevaluation order is defined. A conjunct is evaluated only on rows for which every conjunct written before it was true. Per-row evaluation can raise (integer overflow, out-of-domainSUBSTRING), so this makes the guard-then-test idiom reliable, and every optimizer pass respects it.- Refuse rather than approximate. A scalar subquery returning more than one
row is an error, not a first-row pick. A
STRING-to-numeric join key is an error, not an affinity conversion. Integer overflow is an error, not a promotion. - Release is the default build type. CMake passes no
-Oflag when the build type is empty. Every benchmark before this default existed was an unoptimized measurement, off by roughly 21×.
All TPC-H figures use official dbgen V3.0.1 data. Machine: Apple silicon,
14 cores, 24 GB. Every engine runs the same query text (SwiftQL's dialect port
of the specification). SQLite and PostgreSQL carry the specification's own
primary and foreign key indexes plus ANALYZE. Load time is excluded for every
engine. Latencies are medians after one discarded warmup. Aggregates are
geometric means.
| dataset | modes | match SQLite and survive mutation check |
|---|---|---|
dbgen-sf0.01 |
4 | 20/22 (q16, q17 vacuous at this scale) |
dbgen-sf0.1 |
2 | 22/22 |
dbgen-sf1 |
2 | 22/22 |
At SF=1 all 22 answers also match the published TPC-H answer set under the specification's qualification parameters. "Two modes" means the vectorized executor with and without the optimizer; the Volcano executor answers only 5 of 22 queries by design.
Two independent oracles matter. SQLite runs the same ported query text, so it cannot detect a defect in the port. Adding the published answers immediately found two: q19's port used a shipmode literal the specification does not, and q20's port had replaced its correlated availability test with a trivially true predicate. Both were fixed; q20 went from 45 ms to 505 ms at SF=1 as a result, and that is the correct number.
The mutation check neuters each query's characteristic predicate and requires the answer to change, so no query is counted as correct while asserting nothing.
SwiftQL ÷ engine, geometric mean; below 1.0 means SwiftQL is faster.
| SF=0.01 | SF=0.1 | SF=1 | |
|---|---|---|---|
| vs SQLite | 0.70× | 0.35× | 0.33× (3.0× faster) |
| vs PostgreSQL, default (2 parallel workers) | 0.30× | 0.62× | 0.93× — a tie |
| vs PostgreSQL, single-threaded | — | — | 0.54× (1.9× faster) |
| vs DuckDB (14 cores) | 1.16× | 4.55× | 16.07× |
optimizer gain (--no-optimize ÷ optimized) |
1.92× | 2.22× | 2.44× |
| queries won vs SQLite | 10/22 | 17/22 | 16/21 |
q15 is excluded from SF=1 aggregates: PostgreSQL returns 0 rows where the other
three engines return 1, because the query tests exact equality on a computed
DOUBLE and summation order differs.
The PostgreSQL default comparison at SF=1 is a tie, not a win. A five-repetition run gives 0.93×, but the median run-to-run spread is 11.5% for SwiftQL and 9.2% for PostgreSQL. Recomputing at each engine's extremes gives 0.80× at best and 1.08× at worst, so parity is inside the interval. Earlier three-repetition runs were reported as 1.11× and 1.15× wins; both were inside the noise. No margin under roughly 1.3× on this machine is a result.
The DuckDB gap is parallelism. It widens monotonically with scale, and SwiftQL wins 10 of 22 queries at SF=0.01 but none above that.
Unindexed control. Stripping the specification's indexes from the row
stores separates access path from engine quality. On q22 unindexed SQLite takes
413 s against SwiftQL's 107 ms, because the correlated NOT EXISTS becomes a
nested scan where SwiftQL's planner rewrites it to an anti-join. On q19
PostgreSQL goes from 13.6 ms indexed to 214 ms unindexed, so its win there is
the index. On q18 unindexed PostgreSQL still beats SwiftQL (1 315 ms against
2 718 ms); that loss is engine quality.
The optimizer's contribution grows with scale and is bimodal per query. Queries whose shape it rewrites (decorrelation, derived-table extraction, disjunctive restriction derivation) gain up to 25×; queries with nothing to rewrite (q1, q6, q13, q15, q18, q20, q22) sit at 1.0×.
Cardinality estimates over 276 plan nodes:
| SF=0.1 | SF=1 | |
|---|---|---|
| geometric mean q-error | 4.52 | 6.59 |
| median q-error | 1.59 | 1.86 |
| within 2× / 10× / 100× | 52% / 77% / 92% | 51% / 75% / 83% |
Half of all estimates land within 2×. The tail worsens with scale as the independence assumption compounds along a join spine. One systematic defect: semi and anti joins estimate one row because the subquery body's projection empties its statistics context.
F1 laps table, 1M rows, Release build, average of 5 runs, load excluded.
| Query | Row + Volcano | Columnar + Volcano | Columnar + Vectorized | speedup |
|---|---|---|---|---|
| Full scan aggregate | 76.2 ms | 79.3 ms | 14.1 ms | 5.4× |
| Selective filter + zone-map pruning | 156.0 ms | 47.2 ms | 1.9 ms | 82.8× |
| Projection of 2 of 9 columns | 231.8 ms | 238.5 ms | 40.4 ms | 5.7× |
| GROUP BY dictionary-encoded string | 117.8 ms | 123.3 ms | 21.8 ms | 5.4× |
| Hash join + aggregate | 243.2 ms | 260.5 ms | 114.3 ms | 2.1× |
Columnar storage alone is neutral on scans and pays only where zone maps prune. The vectorized executor is where the gain is.
SELECT COUNT(*) FROM lineitem, wall time and peak RSS of the whole process.
| dataset | lineitem rows |
--storage row |
--storage columnar |
|---|---|---|---|
| SF=0.01 | 60 175 | 0.1 s / 51 MB | 0.1 s / 72 MB |
| SF=0.1 | 600 572 | 1.2 s / 475 MB | 1.7 s / 726 MB |
| SF=1 | 6 001 215 | 14.7 s / 4 521 MB | 20.3 s / 5 450 MB |
Columnar peaks higher than row because the row image is freed only after every table has been converted. SF=1 is the largest scale exercised; SF=10 would need roughly 55 GB by linear extrapolation.
Three artifacts, each able to invert the headline, dominated the first numbers:
- An unset CMake build type made every earlier benchmark a
-O0measurement. Same commit and data, q18 ran 1 568 ms against 72 ms in Release. This alone moved the SQLite comparison from 6.3× slower to 1.9× faster. - The profiler charged subtrees to blocking operators. Three of six
blocking nodes started their timer before draining the child, so every
ORDER BYquery named the sort as its hot node. On q18 the sort reported 42.4 ms where its true self-time is 3 µs. - PostgreSQL's default parallelism is worth 1.73× on its own, and the absence of the specification's indexes changes single queries by up to 3 860×. Both are reported as separate controls.
- Bloom filter join pushdown targeted the join family at the top of 8 of the 10 queries losing to PostgreSQL. Of six targeted queries one moved; the real gains (q8 +55%, q3 +35%) landed on queries already won, and eight queries regress 1–3%. A filter that rejects under 1/8 of its first chunk abandons itself.
- An inline
{key, row}bucket array for the hash join measured slower than the chained index it was meant to replace (60.3 ms against 44.1 ms), because 16-byte slots at a 0.5 load factor make the table 4× larger. - Clustering at load was measured and rejected, as described above.
- No index of any kind. Every remaining loss to an indexed row store has this shape (q17, q19 at SF=1).
- Single-threaded. Accounts for most of the DuckDB gap and all of the PostgreSQL default-configuration gap.
- No persistence. Every invocation re-reads and re-parses the input files. At SF=1 that is 21.9 s before a query that executes in milliseconds.
- Volcano path is a subset. Multi-way joins, subquery joins and derived
tables run on
--execution vectorizedonly. - Join ordering skips any query containing an outer join, including its inner block. An outer join's build side is forced, not costed.
- Cardinality estimation is independence-based. Filters do not narrow column statistics; errors compound multiplicatively along join spines.
SUM/AVGaccumulate indouble, exact only to 2^53.COUNT(DISTINCT)keeps an unbounded per-group set. Nothing spills.- CSV cells cannot express NULL or contain commas. Pipe-delimited
.tblfiles sidestep the comma case. - Not measured: cold start, concurrency, writes, larger-than-memory operation.
Requires CMake ≥ 3.20, a C++17 compiler, and Python 3. GoogleTest and nlohmann/json are fetched automatically.
cmake -S . -B build # Release by default
cmake --build build -j
python3 python_tools/generate_data.py --rows 100000 # F1 sample data
python3 python_tools/generate_tpch.py --scale 0.01 --out-dir data/tpch/sf0.01Run a query:
./build/swiftql --catalog catalog.json \
--storage columnar --execution vectorized \
--query "SELECT team, AVG(speed) FROM laps WHERE season = 2025 GROUP BY team"| Flag | Meaning |
|---|---|
--catalog <path> |
catalog.json describing tables, files and columns |
--query "<sql>" |
Query to run (repeatable) |
--storage row | columnar |
Storage backend (default row) |
--execution volcano | vectorized |
Executor (default volcano) |
--explain / --explain-analyze |
Print the plan / execute and profile each node |
--no-optimize |
Disable the optimizer on the vectorized path |
--no-cache |
Bypass the result cache |
--format tsv |
Machine-readable output |
--storage-stats |
Print encoded column sizes |
Verification gates, in order:
cmake --build build -j
(cd build && ./tests/swiftql_tests) # C++ unit tests
python3 python_tools/compare_against_sqlite.py # SQLite oracle, all modes
python3 python_tools/test_new_queries.py # regression suite + optimizer invariant
python3 python_tools/run_tpch.py \
--catalog data/tpch/sf0.01/catalog.json \
--baseline docs/tpch-baseline.json # 22 queries × 4 modes vs recorded baseline
.venv/bin/python python_tools/validate_answers.py \
--catalog data/tpch/dbgen-sf1/catalog.json # published TPC-H answers (needs dbgen SF=1)Benchmark tooling in python_tools/: compare_engines.py (SwiftQL vs SQLite,
PostgreSQL, DuckDB), qerror_tpch.py (estimate accuracy), benchmark.py (F1
storage/execution comparison), make_figures.py (rebuilds docs/figures/ from
the JSON records in docs/).
CI builds and runs the first four gates on x86-64 with AVX2 and on arm64 with NEON, and checks that the AVX2 join path was actually compiled in.
- development.md — developer guide: build, test, supported SQL with every refusal explained, relation-slot rules, extending expressions.
- docs/week-37-per-query.md — all 22 queries × 5 engines × 3 scale factors.
- docs/week-37-engine-comparison.md — cross-engine methodology and configuration.
- docs/week-37-measurements.md — every measurement taken, including the ones that were not results.
- docs/phase1-vs-phase2.md, phase2-vs-phase3.md, phase3-vs-phase4.md — storage, execution and optimizer benchmarks on the F1 dataset.
- docs/hash-vs-simd-crossover.md — join algorithm crossover calibration.
- Raw records:
docs/week-37-*.json,docs/week-38-*.json,docs/tpch-*.json.