Index Cardinality: Why Some Indexes Don't Help

Cardinality is the number of distinct values in a column relative to the total number of rows. A column storing boolean is_active flags has cardinality of at most 2, no matter how many rows exist; a column storing UUIDs has cardinality equal to the row count, since every value is unique. Cardinality matters because it’s the single biggest factor in whether an index on that column actually helps a query — and it’s the reason “just add an index” doesn’t always speed anything up.

Why cardinality drives index usefulness

An index exists to let the database narrow down which rows to look at without scanning the whole table. It does this by mapping a value to the set of row locations that contain it. If a column has high cardinality — most values are unique or nearly unique — an index lookup for a specific value returns a small handful of rows, which is exactly the kind of narrowing an index is good at.

If a column has low cardinality — say, a status column with three possible values across a million rows — an index lookup for status = 'active' might still return a third of the table. At that point, the query planner usually decides a full table scan is actually cheaper than following hundreds of thousands of index pointers back to the underlying rows, since random-access lookups are more expensive per row than a sequential scan. This is why a status or is_active index is frequently unused in practice, even though it exists and is technically valid: the planner correctly determines it wouldn’t help.

A rough mental model

Think of an index as a phone book sorted by last name. If you’re looking for one of a few hundred people named “Okonkwo-Blackwood,” the sorted list gets you there fast — high cardinality, big win. If you’re looking for everyone whose last name starts with a common letter, jumping into an alphabetically sorted list barely narrows anything down — you’d have been about as fast just reading through the section in order. Low-cardinality columns behave like that common letter: the index technically narrows the search, but not by enough to be worth the extra indirection.

Where low cardinality still helps

Cardinality isn’t the only variable — distribution matters too. A status column with three values isn’t a good index candidate if those values are roughly evenly split, but if 99% of rows are 'completed' and 1% are 'pending', an index on status can be very effective specifically for WHERE status = 'pending' queries, because that value’s row set really is small, even though the column’s overall cardinality is low. Some databases support partial indexes for exactly this case — an index that only covers rows matching a condition, like CREATE INDEX ON orders (id) WHERE status = 'pending' — which stays small and useful regardless of how skewed or even the full distribution is.

Low-cardinality columns are also commonly useful as the second column in a composite index, where a high-cardinality leading column does most of the narrowing and the low-cardinality column adds a small refinement, or lets the index serve as a covering index for a specific query pattern by including it in the index tuple.

Checking cardinality yourself

Most databases expose per-column statistics that estimate cardinality, since the query planner relies on these same numbers to decide whether to use an index at all. In PostgreSQL, pg_stats includes an n_distinct estimate per column, and running ANALYZE on a table refreshes those statistics. A quick manual check is just:

SELECT count(DISTINCT status) AS distinct_values,
       count(*) AS total_rows
FROM orders;

A ratio close to 1 means high cardinality and a strong index candidate. A ratio close to 0 means the column alone is a weak candidate — check EXPLAIN ANALYZE on your actual queries rather than assuming the index is being used just because it exists.

Index types and cardinality sensitivity

Not every index structure reacts to low cardinality the same way. A standard B-tree index — the default in most relational databases and the type most affected by the scan-vs-lookup tradeoff above — is the one most sensitive to this issue. A clustered index, which physically orders the table’s rows rather than pointing to them elsewhere, avoids some of the extra indirection cost, though the fundamental “does this narrow anything down” logic still applies. Hash indexes, where supported, are efficient for equality lookups on any cardinality but don’t help with range queries at all.

The takeaway

An index only earns its cost — extra storage, and extra write overhead on every insert or update — if it lets the planner skip most of the table. High-cardinality columns do that reliably; low-cardinality ones usually don’t, unless the value you’re filtering on is rare within an otherwise skewed distribution. Before adding an index because a query feels slow, check the cardinality of the column you’re about to index, and confirm with EXPLAIN that the planner actually chooses to use it once it exists.