Layer 3 · Go deeper · 6 min
Go deeper
Size it, build it, then make the calls an interviewer will push you on.
Build-up · 6 steps
Read the city revenue plan bottom-up
Ops wants Bengaluru’s ten highest-revenue restaurants over the last 24 hours, from 18 million orders. Read the EXPLAIN ANALYZE tree the way it runs, deepest node first.
Step 1 of 6
Scan restaurants, keep Bengaluru
Execution starts at the deepest node. The Seq Scan keeps 24,000 Bengaluru rows of 200,000; discarding 88% is fine, because the table is small and the scan takes 15 ms.
4 interview moments
You’re the candidate
The interviewer pushes on the hard parts. Pick an approach and see how it holds up.
Deep dive 1 of 4
Interviewer
“The Mysuru zone dashboard is slow. Its Nested Loop planned for 480 outer rows; EXPLAIN ANALYZE shows 48,000 loops. What do you do?”
Your move. Pick an approach:
Deep dive 2 of 4
Interviewer
“The nightly revenue report’s Sort shows
external merge Disk. A teammate wantswork_mem = 256MBin the server config. What do you do?”Your move. Pick an approach:
Deep dive 3 of 4
Interviewer
“
WHERE status = $1is fast when you run it in psql and slow from the app. What do you do?”'pending'is 0.02% of rows and'delivered'is 91%. The app’s driver prepares its statements.Your move. Pick an approach:
Deep dive 4 of 4
Interviewer
“Two suspects. A 20-second nightly report, and a 4 ms query called 2,000 times a second. Which do you explain first?”
Your move. Pick an approach:
Build-up · 5 steps
Find where 480 became 48,000
A zone dashboard filters orders by Mysuru and zone 412 and takes 263 ms. Read up from the deepest node, and stop where estimate and actual split.
Step 1 of 5
The zone estimate is right
The Bitmap Index Scan for zone_id = 412 estimates 48,000 rows and finds 48,000. No gap here, so keep reading upward.