Map: QueryCompare SQLite query-comparison CLI prototype

[matt-t-d]

Destination

A scrappy but runnable local CLI (querycompare) that takes two SQL query files (or args), runs them against a user-supplied SQLite .sqlite file (--db) with rigorous timing (warmup + N iterations, median/p95, cache-state controlled), and prints:

1. Faster verdict + timing distribution — which query won, by how much;
2. EXPLAIN QUERY PLAN diff — side-by-side plans so the user sees why;
3. Result-equivalence / correctness check — warn when the two queries don't return the same thing.

Prototype-quality, throwaway-ish. The map is done when this tool runs.

Notes

• Domain: SQL query-performance comparison on SQLite.
• Execution effort: destination is a working prototype, so this map carries execution into its tickets — overrides Wayfinder's plan-only default. The final ticket produces the runnable tool.
• Locked constraints (from charting grill): local CLI · SQLite only · BYO .sqlite file (--db, no synthetic data-gen) · two queries as files/args · rigorous timing · output = verdict+timings + EXPLAIN QUERY PLAN diff + correctness check.
• Skills: /grilling + /domain-modeling for decision tickets; /research for methodology; /prototype for CLI-output design.

Decisions so far

• Choose implementation language: Go or Rust — Go; leaning mattn/go-sqlite3 (cgo, faithful timing) unless a cgo-free build is required.
• Timing methodology for trustworthy SQLite benchmarking — median headline (min/p95/spread too), verdict = median-ratio gated by significance/overlap check; adaptive iterations (min 10, ≥3 s, cap 100), 3 warmups; round-robin A/B interleave; warm-by-default (cold unportable); one connection + prepared-stmt + reset; EQP out-of-band. Asset: docs/research/sqlite-timing-methodology.md. Surfaced 6 open decisions → inputs to CLI-design Design the CLI interface + output rendering #5.
• Define result-equivalence for the correctness check — full result-set via hashing; multiset / order-insensitive (duplicates significant); positional column shape matters, aliases don't; strict storage-class typing (1≠1.0≠'1'), NULL==NULL; SELECT-only (mutations → explicit N/A, no state-diff); non-blocking warning, exit 0, row counts only (no per-row diff). --ordered + row-diff = post-v1 fog. Feeds output-rendering in CLI-design Design the CLI interface + output rendering #5.
• Design the CLI interface + output rendering — default output = Verdict-first (--format auto|full|compact|json; full=every stat + side-by-side EQP, compact=benchstat-style); write comparison IN v1 via --allow-writes (SAVEPOINT/ROLLBACK, refuse by default); verdict gate default overlap (--significance off|overlap|mannwhitney); adaptive time-budget iterations default (--runs for fixed); warm-only v1 (--cold best-effort v2), loud ANALYZE-missing warning (never mutate file), database/sql driver for prototype. Fully specifies build ticket Build the runnable prototype #6. Asset: prototype branch prototype/cli-output-5 (prototypes/cli-output.mts).
• Build the runnable prototype — ✅ DESTINATION REACHED — the tool runs. querycompare built (Go, main.go+bench.go+stats.go+correctness.go+plan.go+render.go, examples/, README.md) and verified end-to-end on a 200k-row .sqlite: A WINS 154.91× faster, equivalence pass/fail/N/A, EQP index-seek-vs-scan diff, write refuse→allow (rollback pristine), all 4 formats, both gates, go vet clean. Driver pivot: pure-Go modernc.org/sqlite instead of cgo mattn — host cgo toolchain broken (/usr/local/include shadows SDK stddef.h); pre-authorized by Choose implementation language: Go or Rust #2's "unless a cgo-free build is required". Committed in f2751c1 on prototype/querycompare-cli (14 files; examples/db.sqlite gitignored).

Not yet specified

• Binary packaging / distribution — later (post-destination; a fresh effort if pursued).

(All timing / output / correctness / scope fog cleared; destination reached. Post-v1 fog: --ordered per-row result diff; raw-driver fidelity upgrade — swap to cgo mattn if this host's toolchain is fixed and fast-query timing looks polluted.)

Out of scope

• Load / concurrency simulation ("how the DB handles them under load") — deferred to a future effort.
• Non-SQLite engines (Postgres, MySQL, generic).
• Web app / hosted UI / library-API shapes.
• Synthetic data generation — BYO file chosen instead.

[matt-t-d]

Effort complete — destination reached ✅

querycompare is built and merged to `master` via #7 (squash `7586aa6`). Closing this map.

All five child tickets resolved:

• Choose implementation language: Go or Rust #2 — language: Go (pure-Go modernc.org/sqlite driver)
• Timing methodology for trustworthy SQLite benchmarking #3 — timing methodology (adaptive iterations, warm-by-default, round-robin A/B, median-ratio verdict + significance gate)
• Define result-equivalence for the correctness check #4 — result-equivalence (full-set hashing, multiset, strict storage-class typing, non-blocking warning)
• Design the CLI interface + output rendering #5 — CLI interface + output (verdict-first; --format auto|full|compact|json; --allow-writes; significance gate)
• Build the runnable prototype #6 — runnable prototype, verified end-to-end on a 200k-row .sqlite

The full decision trail lives in this map's body.

Out of scope / fresh effort if pursued: binary packaging & distribution — a new /wayfinder map with its own destination, not a reopen of this one.