Selectivity: when an index pays off

The selectivity of a condition is the fraction of rows that satisfy it. email = 'user777@example.com' matches 1 user in 20,000: selectivity 0.00005, very selective. status = 'completed' matches about 95% of orders: selectivity 0.95, not selective at all. The expected number of matching rows (the cardinality) is selectivity × table rows.

Why does it matter? An Index Scan pays for every match separately: it walks to an index entry and then jumps to the row's page in the table. Those jumps land on pages all over the table (random access). A sequential scan reads every page once, in order, which is much cheaper per page. When the matching rows are few, jumping to them wins. When they are many, they are spread over almost every page anyway, and reading the whole table in order is cheaper than jumping thousands of times. The planner's default settings price a random page read at 4× a sequential one (random_page_cost = 4, seq_page_cost = 1).