Indexing a low-cardinality status column

WHAT THIS TESTS The interviewer probes whether you understand that index value comes from selectivity, and that the optimizer rationally skips weak indexes.

A GOOD ANSWER COVERS An index helps when a predicate is selective, meaning it narrows to a small fraction of rows. With a status column of three values, each value matches about a third of the table. Using the index means reading many index entries then following each pointer to a different heap page, which is scattered random I/O. Reading a third of a table that way is usually slower than one sequential scan that streams pages in order, so the cost-based optimizer correctly ignores the index. The fix depends on the real query. A partial index, defined with a WHERE clause, indexes only a small interesting subset, for example only rows where status equals a rare value like pending, producing a tiny, highly selective index used for exactly that query. A composite index leading with status followed by a selective column lets queries that also filter that column seek efficiently. For mostly-archived data you can index only the active rows. Bitmap indexes serve this pattern in analytic engines, though row-store OLTP engines often lack them.

COMMON WRONG ANSWERS Assuming any index always speeds a filter. Adding a plain status index and being surprised the optimizer ignores it. Forgetting that random heap access on many rows beats a scan only when rows are few.

LIKELY FOLLOW-UPS What is selectivity numerically; when does the optimizer flip from scan to index; how does a partial index stay small; why are bitmap indexes good here.

ONE CONCRETE EXAMPLE An orders table is 99 percent completed and queries repeatedly fetch the few pending rows. A full B-Tree on status is ignored. CREATE INDEX idx_pending ON orders (created_at) WHERE status = 'pending' builds a tiny partial index covering only pending rows, which the optimizer uses for an instant seek instead of scanning millions of completed rows.