Cardinality
Cardinality refers to the number of distinct values in a column or dataset, or, in data modeling, the nature of a relationship between two tables (such as one-to-many).
Overview
"Cardinality" shows up in two related but distinct contexts in databases: as a property of a column's data (how many distinct values it has), and as a property of a relationship between tables in a data model (how many rows on one side can relate to rows on the other).
Column Cardinality
A column's cardinality is its number of distinct values relative to its total row count. A boolean or status column with only a handful of possible values has low cardinality; a primary key or UUID column, where every value is unique, has high cardinality. Cardinality matters a great deal for both compression (low-cardinality columns compress extremely well with dictionary encoding) and query optimization (a filter on a high-cardinality column is usually far more selective, and therefore more useful to index or prune on, than a filter on a low-cardinality one).
Cardinality in Data Modeling
In schema design, cardinality describes the shape of a relationship: one-to-one, one-to-many, or many-to-many. A customers-to-orders relationship is typically one-to-many — one customer can have many orders, but each order belongs to exactly one customer — which determines how foreign keys and join cardinality behave in queries against that schema.
Why Cardinality Matters for Query Optimization
A cost-based optimizer relies heavily on cardinality estimates — how many rows will come out of a scan, filter, or join — to choose things like join order and join algorithm. Getting these estimates right is one of the hardest and most consequential parts of query optimization; a badly wrong cardinality estimate is one of the most common causes of a slow query plan.
DuckDB and Cardinality
DuckDB's optimizer maintains per-column statistics, including distinct-value counts, to estimate cardinality for join ordering and other decisions. For getting an exact or approximate distinct count directly, DuckDB provides count(DISTINCT col) for an exact answer and approx_count_distinct(col), based on the HyperLogLog algorithm, for a much faster approximate answer on large columns:
Copy code
SELECT approx_count_distinct(customer_id) AS approx_unique_customers
FROM orders;
EXPLAIN ANALYZE also reports each plan operator's estimated cardinality (EC) next to its actual row count, which is a useful way to spot where the optimizer's estimates went wrong.
Related terms
HyperLogLog is a probabilistic algorithm for estimating the number of distinct elements in a large dataset (cardinality) using a small, fixed amount of memory, at the cost of a small, predictable error margin.
Cost-based optimizer →A cost-based optimizer chooses among logically equivalent query plans by estimating the execution cost of each one, using statistics about table sizes and data distributions, and picking the cheapest estimate.
DISTINCT →DISTINCT removes duplicate rows from a query's result set, returning only unique combinations of the selected columns.
Data profiling →Data profiling is the process of examining a dataset to understand its structure, content, and quality — things like data types, value distributions, null rates, and cardinality — before using it for analysis or building pipelines on top of it.
Query optimization →Query optimization is the process by which a database transforms a SQL query into an efficient execution plan, choosing among logically equivalent strategies for the one expected to run fastest.
Execution plan →An execution plan is the tree of operators a database will run, in a specific order, to produce the result of a query — the concrete strategy chosen by the query planner and optimizer.
FAQS
Low cardinality means a column has relatively few distinct values compared to its row count (like a status flag). High cardinality means most or all values are unique (like a UUID or primary key).
Use approx_count_distinct(column), which uses the HyperLogLog algorithm to estimate distinct counts much faster than an exact count(DISTINCT column) on large data.
Cost-based optimizers use cardinality estimates to decide things like join order and join algorithm. Inaccurate cardinality estimates are one of the most common causes of a slow, poorly chosen query plan.
