cardinal

n/a downloads this week 1 star Extension descriptor

Better cardinality estimates for DuckDB's join-order optimizer, learned from your tables with Bayesian networks

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 cardinal FROM community;
LOAD cardinal;

Example

-- Moderators, 1% of users, make half of the edits.
CREATE TABLE users AS SELECT range AS id, range % 100 = 0 AS moderator FROM range(10000);
CREATE TABLE edits AS
SELECT CASE WHEN range % 2 = 0 THEN (range // 2) % 100 * 100 ELSE range % 10000 END AS user_id
FROM range(100000);

-- The join's estimate is ~59,000 rows with cardinal, ~20,000 without. The true count is 50,000.
EXPLAIN SELECT count(*) FROM users u, edits e WHERE u.id = e.user_id AND u.moderator;

-- Plan without cardinal, for this connection.
SET cardinal_estimator = 'off';

About cardinal

DuckDB picks a join order from estimates of how many rows each join produces. Those estimates assume columns are independent, so on real data they can be off by orders of magnitude.

cardinal learns a Bayesian network per table, the first time a query needs it, and gives DuckDB its estimates instead. There is nothing to configure. Results never change, only plans. On the STATS-CEB benchmark, total runtime goes from 130 s to 67 s.

Models are saved in ~/.duckdb/extension_data/cardinal/, never in your database. Only DuckDB tables are estimated: queries on Parquet or CSV files are planned by DuckDB alone.

Documentation, benchmarks and limits: https://github.com/MaxHalford/cardinal

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings