This trace follows the actual state transitions behind the companion How a B-Tree Index Narrows a Database Search. It describes a common execution path; implementation details can vary, so keep the contract separate from the mechanism.
Step 1: Compare key at an index page
A database index is an auxiliary structure that helps find rows without scanning every table page. A B-tree keeps keys in sorted order across pages and uses separator keys to direct a search from the root toward a leaf. The index is valuable when its lookup cost is lower than the work it avoids.
Step 2: Choose the child page
For a query filtering by customer_id and ordering by created_at, a composite index beginning with customer_id may narrow to one customer’s key range and then return rows in order. If the query selects columns present in the index, some engines may avoid visiting table pages for each result.
Step 3: Reach the matching leaf range
A B-tree search repeatedly narrows the key range using separator values; whether the index helps overall depends on how many table pages the query still needs.
At this point, record the state that changed and check the invariant before advancing. If the operation repeats, make clear which values persist and which are recomputed.
Step 4: Fetch qualifying table rows
Indexes consume storage and add work to inserts, deletes, and updates. A low-selectivity column may not justify an index by itself, and a query planner can prefer a sequential scan when many rows qualify. Composite index column order changes which predicates can use its leading range efficiently.
Step 5: Maintain index pages on writes
Given an index on (tenant_id, created_at), compare queries filtering by tenant_id alone and created_at alone. Explain what information the index ordering makes directly available.
The trace is complete when the result satisfies the stated contract. Compare this model with the concrete runtime or system you are studying before making a performance claim.