Learning series · 5 episodes · Senior / Staff
The database has a plan
Five original interview lessons on access paths, composite sort order, parameter-sensitive plans, hash join spills and MVCC visibility.
Start series →Follow the decisions in order
- Part 1 of 5
Senior
We added an index. Why is the full table scan still faster?Read-path economics with correct selectivity: random heap fetches versus sequential scan; why index presence does not establish a faster access route.
- Part 2 of 5
Senior
The index has all three columns. Why is there still a Sort?Composite B-tree key ordering and ORDER BY satisfaction across multiple equality groups; indexes do not promise one global ordering across grouped ranges.
- Part 3 of 5
Staff
The SQL is identical. Did PostgreSQL really use the same plan?Planner statistics and prepared-statement generic-vs-custom plan choice under extreme tenant skew, distinguished from correct high-volume work.
- Part 4 of 5
Staff
A hash join spilled gigabytes. Should we just add memory?Memory and I/O behavior of a hash join crossing its working-set threshold, including batch spill, concurrent memory multiplication, and remediation.
- Part 5 of 5
Staff
Index Only Scan still hits the heap. What is it checking?Why index-only scans still consult heap MVCC visibility via the visibility map, and how volatile rows, vacuum, and HOT index-maintenance constraints change the tradeoff.