tessera

539 downloads this week 6 stars Extension descriptor

Fast join-aggregates (pair counts and sums of self-joins and two-relation joins) over skewed sparse relations, with a sparse product and a CPU matrix accelerator

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Installing and Loading

INSTALL tessera FROM community;
LOAD tessera;

Example

CREATE TABLE r AS
  SELECT (random() * 20000)::INTEGER AS k, floor(power(random(), 3) * 5000)::INTEGER AS x FROM range(2000000);
-- the five pairs of values that share the most keys; Tessera replaces the join and the aggregation
SELECT a.x AS x0, b.x AS x1, COUNT(*) AS c
FROM r a JOIN r b ON a.k = b.k AND a.x < b.x
GROUP BY a.x, b.x ORDER BY c DESC LIMIT 5;
EXPLAIN SELECT a.x, b.x, COUNT(*) FROM r a JOIN r b ON a.k = b.k GROUP BY a.x, b.x;
-- item similarity above a threshold
SELECT * FROM tessera_sim('r', 'k', 'x', 'jaccard', 0.2);

About tessera

Tessera is an optimizer rule and operator for join-aggregates, queries that count or sum the joined rows of each pair of values: SELECT a.x, b.x, COUNT(*) FROM R a JOIN R b ON a.k = b.k GROUP BY a.x, b.x, also with SUM(a.v * b.v), AVG, COUNT(DISTINCT a.k), a.x < b.x, a.x <> b.x, HAVING, ORDER BY ... LIMIT, the top values of each group (RANK() OVER (PARTITION BY a.x ORDER BY ...) <= k), several sums in one query such as a cosine, further group columns, keys of several columns, NULLs in group and summed columns, filters on the inputs, or a second relation, over tables, pandas DataFrames, and Parquet and CSV files. It never builds the joined rows. It computes each pair once on a sparse path, drops the values that cannot reach a HAVING or top-k threshold before any product, and runs the dense block of the most frequent values on Intel AMX where the CPU has it. The rule replaces matching plans by itself, so the SQL stays unchanged. Settings: tessera_enabled, tessera_frontier, tessera_min_expansion, tessera_min_expansion_cross, tessera_min_expansion_measured. See https://github.com/GyuyeongKim/Tessera-public for the supported queries, the PostgreSQL, SQLite, and Spark adapters, and the C interface.

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings