What makes a composite index effective, and what is index selectivity?

#Selectivity: the number that decides whether an index helps at all

Before talking about composite indexes, you need the concept that determines whether any index is worth having on a column: selectivity. Selectivity is the ratio of distinct values to total rows: distinct_values / total_rows, a number between 0 and 1. A column like email on a users table is highly selective — nearly every row has a unique value, so an equality lookup on it eliminates almost every other row immediately. A column like status with three possible values (active, pending, closed) on a million-row table is poorly selective — an equality match on status = 'active' might still return 300,000 rows, and walking an index to fetch 300,000 scattered row pointers can be slower than just scanning the table sequentially.

#Composite indexes and the leftmost-prefix rule

A composite (multi-column) index is a single B-tree keyed on more than one column, sorted first by the first column, then by the second column within ties on the first, and so on. That ordering has a direct consequence, usually called the leftmost-prefix rule: a composite index on (a, b, c) can efficiently serve a query that filters on a, or on a and b, or on a, b, and c — but it generally cannot efficiently serve a query that filters on b alone, or c alone, or (b, c) without a, because the tree isn't sorted by b independent of a.

-- orders(customer_id, status, created_at, total_cents)
CREATE INDEX idx_orders_customer_status_date
    ON orders (customer_id, status, created_at);

-- Uses the FULL index efficiently: constrains customer_id (leftmost),
-- status (second), and created_at (third) in order.
SELECT * FROM orders
WHERE customer_id = 42
  AND status = 'shipped'
  AND created_at > '2026-01-01';

-- Uses only the customer_id prefix of the index (still helpful,
-- just not as narrowing as the query above).
SELECT * FROM orders
WHERE customer_id = 42;

-- Cannot use this index at all: it skips the leftmost column customer_id.
SELECT * FROM orders
WHERE status = 'shipped'
  AND created_at > '2026-01-01';

That last query would need a different index — either (status, created_at) on its own, or (status, created_at, customer_id) — depending on what other queries also need to be fast, because you're rarely optimizing for one query in isolation.

#Column order: equality before range

Column order in a composite index isn't just about which column is "more important" — it's about which predicates are equality and which are range. A B-tree can only stay sorted usefully for a trailing column if every column before it is constrained to a single value (an equality match). The moment a column has a range predicate (>, <, BETWEEN), everything after it in the index stops being useful for further narrowing, because within that range the following column isn't sorted in any way that helps.

-- Good: equality column (customer_id) first, range column (created_at) last.
CREATE INDEX idx_good ON orders (customer_id, created_at);

SELECT * FROM orders
WHERE customer_id = 42
  AND created_at BETWEEN '2026-01-01' AND '2026-02-01';
-- The tree narrows to exactly customer_id = 42, then within that
-- narrow slice, created_at is sorted, so the range scan is a tight
-- walk along the leaf chain.

-- Bad: range column first, equality column second.
CREATE INDEX idx_bad ON orders (created_at, customer_id);

SELECT * FROM orders
WHERE customer_id = 42
  AND created_at BETWEEN '2026-01-01' AND '2026-02-01';
-- The tree narrows by created_at first, producing a wide slice
-- containing every customer in that date range, and customer_id
-- isn't sorted within that slice, so it can't narrow further —
-- the engine has to check customer_id row by row across the slice.

#Covering indexes: skipping the table entirely

A composite index that happens to contain every column a query needs — in the filter, the sort, and the SELECT list — lets the database answer the query straight from the index, without ever touching the underlying table (an "index-only scan" in Postgres? terms). Adding non-key columns purely for this purpose is usually done with an INCLUDE clause so they ride along in the index's leaf pages without being part of the sort key:

CREATE INDEX idx_orders_covering
    ON orders (customer_id, status)
    INCLUDE (total_cents);

-- total_cents is retrievable straight from the index leaf pages,
-- so this query never has to visit the orders heap table at all.
SELECT total_cents FROM orders
WHERE customer_id = 42 AND status = 'shipped';

#Selectivity by example

When you're asked this in an interview, walk through selectivity first (does an index even help here?), then the leftmost-prefix rule (does the column order match how the query filters?), then equality-before-range (does the order let the tree stay narrow for as many columns as possible?) — in that order, that's exactly how a query planner reasons about it too.