CoursesBackend engineering from first request to production
Puzzle 8 · 9 of 18
Why does the same page cost less to read?
Inspect a query plan before and after adding an index.
All puzzles in this course
The problem
A route lists five recent bookmarks. Reading five rows sounds cheap. What if the database must inspect two thousand candidates first?
See it
Scroll sideways to see the full diagram.
The query selects one owner.
Step 1 of 4Time plus row number gives a stable order.
Step 2 of 4The index stores keys in the requested order.
Step 3 of 4Check that both versions return the same five rows.
Step 4 of 4
The building block
The data drill generates repeated timestamps and several owners. The query filters by owner and sorts by time, then by a unique row number to settle ties. Without a matching index the database scans the table and uses temporary storage for sorting in this example. An index arranged by owner, time and row number can locate the matching range in order. Compare the plans and the actual rows. Extra indexes occupy space and must be maintained on writes. For the next page, carry the last time and row number together and continue below that pair using the same ordering.
Trace the page boundary
Use ascending order for this small example. The rows for one owner are (time 10, id 1), (10, 2) and (11, 3). With a page size of one, the first response ends at (10, 1). A next-page filter requiring a time greater than ten jumps straight to row 3. A filter accepting a later time or the same time and a larger row number returns row 2. For descending order, reverse the direction of both the ordering and continuation comparison. First verify the pages contain the right rows in order. Then inspect the query plan to learn how much searching and sorting produced them.
Try it
Optional local lab
Run the data drills, inspect both plans and implement a second page. Check that two tied rows across the page boundary are each returned once.
Check yourself
Sources
What you now know
An index matching the owner and sort order can serve the same page with less searching and sorting.
Answer every question above correctly to unlock the next puzzle.
Something wrong or unclear on this page? Tell me: a puzzle that didn't click is a bug in the puzzle.