A strong answer
A B-tree index keeps keys sorted across fixed-size pages (commonly 8 KB) so a search narrows by range instead of scanning. Each internal page holds separator keys that direct the descent: at the root, compare the target against the separators and follow the single child pointer for that range, repeating until a leaf page yields the matching entries with pointers to table rows. With a fanout of several hundred entries per page, a table of 100 million rows needs only 3 to 4 levels, so a point lookup touches 3 to 4 index pages plus the heap pages for the rows themselves — O(log n) page visits instead of O(n).
A composite index on (tenant_id, created_at) sorts by the first column, then the second within each tenant, which is why column order controls usability. Filtering by tenant_id = 42 seeks directly to that tenant's contiguous range and can return rows already ordered by created_at. Filtering by created_at alone cannot use the leading range: the matching timestamps scatter across every tenant's section, so the engine scans far more of the index. The same structure decides the write cost: each insert, update, or delete modifies every index containing the affected columns, adding one or more page writes per index plus storage roughly proportional to the indexed columns.
The planner weighs that lookup cost against a sequential scan. When a predicate matches 1,000 rows out of 10 million (0.01% selectivity), a few thousand page visits beat reading the whole table. When it matches 3 million rows (30%), chasing 3 million scattered heap pointers with random I/O loses to a single ordered pass over the table, and the planner picks the scan. An index on a low-selectivity column such as a boolean flag rarely pays: it narrows almost nothing while taxing every write.
What the interviewer is listening for
- Root-to-leaf descent through separator keys with fanout, depth, and page-visit math.
- Why
(tenant_id, created_at)serves a tenant filter plus ordering but not a bare timestamp filter. - The scan-versus-index crossover tied to selectivity and random versus sequential page access.
- Write-path costs: extra page writes per index and storage growth.
Common mistakes
- Saying indexes make every query faster without mentioning selectivity or heap fetches.
- Ignoring column order in a composite index.
- Forgetting that each additional index taxes inserts, updates, and deletes.
- Assuming a sequential scan means the index is broken rather than the cheaper plan.