Query plans

console.log(
  await db.explain(`
    SELECT c.city, COUNT(*) AS orders
    FROM orders o JOIN customers c ON c.customer_id = o.customer_id
    WHERE o.status = 'completed' AND o.total > 100
    GROUP BY c.city
  `),
);

The plan is the optimized one — what will actually run, not the shape you wrote.

What the optimizer does

Early filtering. Filters move as close to their table scan as they can get, so rows are discarded before a join builds a hash table out of them. A filter on a derived table or CTE moves inside the block when it names a plain projected column, but never past a LIMIT or OFFSET, literal or placeholder: filtering before the window would change which rows the window keeps.

Same-column OR becomes IN. An OR of equalities on one column against literals or parameters — status = 'a' OR status = 'b' — rewrites to status IN ('a', 'b'), which runs as a vectorized membership test and can skip blocks by value range. Any other OR keeps its shape and is evaluated per row.

Constants cross join keys. An inner equi-join or a semi-join (a decorrelated EXISTS) makes its two key columns equal, so an equality, IN list, or range on one side reaches the other: WHERE c.customer_id = 42 with ON o.customer = c.customer_id also filters orders by customer = 42 before the join, through a secondary index when one exists, or through the unique key when the constant names it. A placeholder is a constant here too: WHERE c.customer_id >= ? mirrors the bound value the same way. A LEFT join keeps its null-extended rows, which a mirrored predicate would fail, so it gets none.

Calendar equalities become ranges. DATE_TRUNC('month', placed_at) = TIMESTAMP '2025-06-01 00:00:00' and EXTRACT(YEAR FROM placed_at) = 2025 are planned as placed_at >= start AND placed_at < start + 1 unit, which skips blocks by value range and runs on the raw datetime kernel instead of computing a calendar value per row. A timestamp not aligned to its unit can never equal a truncation, so that predicate is a constant false. With a placeholder (DATE_TRUNC('day', placed_at) = ?) the same rewrite runs when the value is bound, so a parameterized query reads the same blocks as its literal form.

Skipping by value range. Every block records the minimum and maximum of the column it holds. A comparison that cannot be true inside a block's range skips the block without decoding or decompressing it. This is why WHERE placed_at >= '2025-01-01' on a table written in date order reads a fraction of the bytes.

Reading only needed columns. Only the columns a query mentions are read. A SELECT of two columns from a fourteen-column table reads two columns' worth of blocks.

Join reordering. Minnow estimates how many rows each side will produce and builds its lookup from the smaller side. An equality join can use a table's unique-key lookup instead. Pure comma/CROSS-join groups keep the streamed base fixed, then choose connected sources from the WHERE equality graph and turn one safe equality per source into a hash-join key. A permuted FROM list therefore does not force the executor through disconnected Cartesian products.

Decorrelation. A correlated EXISTS or NOT EXISTS subquery is rewritten into a semi-join or anti-join rather than executed once per outer row; both equality and range comparisons take this path. Correlated scalar aggregate expressions and ordinary single-row projections join distinct outer probe values to the inner rows; per-probe window ranks implement their ordering, limits, and offsets before the grouped value joins back. The probe block carries the outer WHERE conjuncts that read only its own sources, so WHERE o.id BETWEEN ? AND ? probes a hundred keys rather than every key in the table, and every equality-correlated scalar — the plain grouped aggregate included — mirrors those key conjuncts onto its inner scan as well, so a WHERE c.id = ? outside becomes o.customer_id = ? inside and the inner scan takes that column's index. Correlated IN uses equality-key joins directly or distinct probes for a range comparison. An uncorrelated column IN (SELECT …) is a join to the subquery's distinct values, so a constant on the column reaches the subquery's own scan through the join key instead of the subquery materializing every value first; NOT IN and a probe that is an expression rather than a column keep the membership test. Correlated NOT IN carries explicit empty-set and NULL flags alongside its anti-join, including for range comparisons. Below OR, NOT, CASE, or a select expression, IN, NOT IN, ANY, and ALL group true, false, unknown, and empty-set state per distinct outer probe tuple. Generated aliases are unique across the complete plan tree. When an outer reference occurs only inside a deeper scalar or boolean expression, distinct outer probe tuples lift that reference into the nested plan before ordinary decorrelation. See the feature matrix.

Top-N. ORDER BY … LIMIT n keeps only the best n rows as it scans instead of sorting the whole input, while n is under a tenth of the rows scanned. A page deeper than that keeps every row and sorts once, which is faster than maintaining the bound.

Sorting. Numbers, datetimes, booleans, and strings sort as typed keys, not by comparing values: strings are ranked through their distinct set first. Input that already arrives in order is recognized and not sorted again.

Execution

Keyed point reads. A single-table WHERE that is a conjunction of column = value equalities covering the table's unique key — scalar or composite — names at most one row, so it skips the batch pipeline entirely and answers from cached decoded blocks: binary search over ascending key components, dictionary probes for string keys. Projected columns may carry a logical domain — NUMERIC, UUID, JSON, DATE, enums, arrays — and render exactly as the ordinary executor renders them, declared-scale padding included. Pending update and delete deltas do not cost a scalar key its fast path: the key's own history is replayed in visible order — an insert lands the row, a delete removes it, an update patches the columns it changed — so a row read right after it was updated still answers in microseconds. A composite key with pending deltas, a cross-type comparison, a view, or a domain-typed column in the WHERE clause falls back to the ordinary executor, which stays the authority on answers and errors. EXPLAIN reports eligibility as key-covering equality answers as a point read when the visible history allows.

One value at a time, not one row. A predicate that reads exactly one string column — LOWER(status) = 'paid', COALESCE(region, 'none') = 'none', a CASE over one column — is evaluated once per distinct value of that column and applied by dictionary code. So is a conditional aggregate: SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) decides the branch once per distinct status and then reads the number column directly. A GROUP BY over one string column or an expression of it, or over one numeric or datetime column, resolves its group the same way, and a GROUP BY over several bare columns — string, number, or datetime — packs one code per column into a single slot. Plain number and string comparisons, number arithmetic, whole-number ROUND, and the calendar functions run as typed operations without domain checks per row.

The query runner works on batches of column values rather than one row at a time, and scans stream block by block so a table is never required to be resident. When a hash table, sort, or grouping payload would exceed the memory budget, the operator spills to storage and continues rather than failing.

Measuring a query honestly

Repeating one statement over unchanging data measures the result memo, not execution. In a timing loop, turn it off:

const start = performance.now();
await db.query(sql, { memoize: false });
const elapsed = performance.now() - start;

onStats reports what an execution actually cost, including peak modeled memory — something the engine can report because it reserves before it allocates. It works through MinnowDatabaseClient too; the callback stays on the page and receives an ordered worker event:

await db.query(sql, {
  memoize: false,
  onStats: (stats) => {
    console.log(stats);
  },
});

The benchmarks page runs the full read and write suites against SQLite WASM and PGlite in your own browser, on a dataset size you pick. Read columns measure engine execution inside the benchmark worker, with optional cached repeats shown separately. The live-query suite uses MinnowDatabaseClient and times a commit until every affected subscription has been notified, including notification delivery across the worker channel. Only Minnow’s subscription drivers are implemented in this harness.