Selectivity, Cardinality, and Index Candidates

Learning outcomes

Not every frequently filtered column deserves an index. A useful index candidate depends on how many rows exist, how values are distributed, how selective the predicate is, how often the query runs, and how expensive writes are. Cardinality and selectivity provide the vocabulary for reasoning about this.

Table cardinality

In database discussions, cardinality can mean several related things. In query planning, it often refers to the estimated number of rows produced by an operation. In data modeling, it also describes relationship multiplicity. Here we focus on row counts and distinct values.

Distinct-value cardinality

Suppose WorkOrder has 10 million rows:

status_code       6 distinct valuesasset_id          2.5 million distinct valueswork_order_id    10 million distinct values

These columns have very different value cardinalities.

Selectivity

Predicate selectivity is roughly the fraction of table rows that match a condition.

If:

WHERE work_order_id = 84217

matches one of 10 million rows, it is extremely selective.

If:

WHERE status_code = 'closed'

matches 7 million rows, it is not selective.

Why selectivity matters

An index lookup is useful when it avoids reading large amounts of irrelevant data. If a condition matches most of the table, the optimizer may prefer a sequential scan because following millions of index entries back to table rows can cost more.

Low-cardinality does not mean “never index”

A boolean or status column may still be useful when the filtered subset is small. Example:

is_deleted = false

is weak if 99.9% of rows are false. But:

is_deleted = true

may identify a tiny subset. A partial index may target exactly that subset.

Distribution matters

Suppose WorkOrder status distribution is:

closed        80%cancelled      8%in_progress    5%scheduled      4%open           2%blocked        1%

An index on status_code may be valuable for rare statuses but unattractive for closed.

Skew breaks uniform assumptions

Statistics help the optimizer understand whether some values are much more common than others. Without good statistics, it may estimate poorly and choose an inefficient plan.

Frequency matters too

A highly selective query run once per month may not justify an index that slows every write. A moderately selective query executed thousands of times per second may justify significant indexing effort.

Predicate shape matters

These are not equivalent from an indexing perspective:

WHERE customer_id = ?WHERE lower(email) = ?WHERE opened_at BETWEEN ? AND ?WHERE description LIKE '%pump%'WHERE status_code IN ('open','blocked')

Different predicates may need ordinary, expression, range-friendly, or specialized indexes.

Sargability

A predicate is often called sargable when the database can use an index search argument effectively. For example:

WHERE opened_at >= :start  AND opened_at < :end

is generally friendlier than wrapping the indexed column in a function that prevents direct lookup.

Expression indexes can rescue intentional expressions

If business semantics require case-insensitive lookup:

WHERE lower(email) = lower(:email)

an expression index on lower(email) may align the physical structure with the predicate.

Join selectivity

Index candidates are not limited to WHERE columns. Join keys matter because they determine how efficiently related rows can be located.

WorkshopHub workload examples

Estimate rows before indexing

For a candidate query, estimate:

• table row count;
• rows matching equality predicates;
• rows matching range predicates;
• rows after joins;
• rows ultimately returned to the application.

Index candidates from access patterns

Create an access-pattern inventory:

Q1: get Part by SKUQ2: list WorkOrders by Asset ordered newest-firstQ3: list open WorkOrders by CustomerQ4: list active assignments by TechnicianQ5: search parts by description text

Then design indexes to support the highest-impact queries.

Do not index every foreign key blindly

Foreign keys are strong candidates, especially for joins and parent modification checks, but tiny tables or rarely queried relationships may not need dedicated indexes. Measure and inspect plans.

Statistics can become stale

Large data changes can invalidate optimizer assumptions. Maintenance processes such as ANALYZE/statistics refresh are part of physical design operations.

Practice: rank index candidates

Summary and next lesson

Selectivity, cardinality, skew, predicate shape, and workload frequency determine whether an index is worth its cost. The next lesson moves from single-column candidates to composite indexes, where column order determines which query shapes can use the index efficiently.

References

• PostgreSQL documentation on planner statistics and indexes.
• Markus Winand, SQL Performance Explained.
• Use The Index, Luke! documentation and examples.