Lazy queries and explain()

Table methods such as filter, derive, join and sort run one at a time: each builds its result before the next step starts. t.lazy() instead records the steps, optimizes the whole chain, and runs it only when you ask for an answer. The result is the same; the work is often much less.

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  customer: [1, 2, 1, 3, 2, 1, 3, 4],
  region:   ['EU', 'US', 'EU', 'APAC', 'US', 'EU', 'APAC', 'EU'],
  price:    [120, 80, 45, 300, 99, 15, 60, 10],
  qty:      [2, 1, 4, 1, 3, 10, 2, 5],
});

const q = orders.lazy()
  .derive('revenue', 'price * qty')
  .filter("region == 'EU' && revenue > 100")
  .groupBy('customer').agg({ spend: 'sum(revenue)', orders: 'count()' })
  .sort('-spend').limit(10);

console.log(q.explain());
console.log(q.toRows());
await engine.close();

Nothing ran until toRows(). The plan shows what the planner did: the revenue column was never built (its expression was used in the filter and the sum instead), the filter on region and revenue runs straight on the scan, and sort plus limit became a top-k.

Steps

Step	Does
filter(expr, params)	keep rows where expr is true
derive(name, expr, params)	a new column
select(...names)	keep only these columns
join(other, opts)	a join; other is a table or another lazy query
groupBy(keys).agg(spec)	a group-by; the result is a lazy query again, so you can filter or sort the groups
sort(by)	sort by columns or expressions
limit(n, offset)	the first n rows, after skipping offset
top(k, by)	the same as sort(by).limit(k)

Each step returns a new query and leaves the one it was called on unchanged, so a base query can be shared and extended in different directions.

params work as on tables: filter('price > min', { min: 100 }). They become constants in the plan, and a Date becomes its milliseconds.

Running a query

Method	Returns
collect()	a new table
toRows(limit)	plain objects, at most limit of them
agg(spec)	one object of totals over the query’s rows, e.g. { units: 'sum(qty)' }
count()	the number of rows
explain()	the optimized plan as text, one step per line

Each has an ...Async version (collectAsync, toRowsAsync, aggAsync, countAsync) that runs on the worker threads without blocking yours.

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  customer: [1, 2, 1, 3, 2, 1],
  region:   ['EU', 'US', 'EU', 'APAC', 'US', 'EU'],
  price:    [120, 80, 45, 300, 99, 15],
  qty:      [2, 1, 4, 1, 3, 10],
});

const eu = orders.lazy().filter('region == r', { r: 'EU' });   // a base query

console.log(eu.count());
console.log(eu.agg({ units: 'sum(qty)', avgPrice: 'mean(price)' }));
console.log(await eu.top(2, '-price').toRowsAsync());

const small = eu.select('customer', 'qty').collect();          // a real table
console.log(small.columnNames, small.length);
small.dispose();
await engine.close();

A table from collect() holds its own memory; dispose() frees it when you are done.

What the planner does

Derived columns are inlined. A filter, aggregate or sort key that uses a derived column uses its expression instead, so the column is never built. If the column is needed as a column (in the output, as a group key or a join key), it is built after the filters, for the matching rows only. Derived columns nobody uses are dropped.

Filters move down. A filter moves below derives (by inlining), below selects and sorts, below a group-by when it uses only the group keys, and into each side of a join when it uses only that side’s columns. Rows are dropped before the expensive steps rather than after.

Only used columns are carried. Joins, sorts and collects copy just the columns that later steps or the output need.

sort + limit becomes top-k, which doesn’t sort every row. With an offset, it takes the first n + offset and skips the offset.

Constants are folded. Parameters become constants, and the kernel compiler computes constant parts of an expression once, and repeated parts once per row.

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  region: ['EU', 'US', 'EU', 'APAC', 'US', 'EU'],
  qty:    [2, 1, 4, 1, 3, 10],
});

const q = orders.lazy()
  .groupBy('region').agg({ n: 'count()', units: 'sum(qty)' })
  .filter("units > 5 && region != 'US'");

console.log(q.explain());
console.log(q.toRows());
await engine.close();

The region != 'US' part uses only the group key, so it runs on the rows before grouping (the plan says “1 filter(s) on keys moved below”). units > 5 needs the totals and stays after.

Reading explain()

The plan prints innermost step first: the scan at the top, the final step at the bottom. A join prints its two sides indented under left: and right:. Lines you will see:

• scan [columns]: the table and its columns. On a dataset it also says how many chunks it has and that chunks the filters rule out are skipped.
• filter ...: each condition, after inlining and moving.
• derive name = ... (inlined where used).
• groupBy keys: ..., with a note when filters on keys were moved below it.
• top k by key (sort + limit), or sort and limit when they stand alone.

SQL queries compile to the same plans, so sql(...).explain() works too. Names starting with __ in a SQL plan are the compiler’s own temporary columns.

How much it saves

From the README (npm run bench:plan, 10M orders, the same chain run step by step vs as one lazy query):

Chain	Step by step	Lazy
derive + filter + total	19.8 ms	2.9 ms
join, then filter	144 ms	22 ms
sort, then first 10	177 ms	12 ms

Computing repeated parts of an expression once per row took sum(year(ts)) from 26 ms to 7.5 ms.

Lazy queries on datasets

dataset.lazy() works the same way on a dataset bigger than memory. The planner’s filters are also used to skip chunks, and toRows(limit) reads only as many rows as it needs.