A strong answer
Start with measurement, not guesses: run EXPLAIN (ANALYZE, BUFFERS) and read three numbers together — elapsed time, buffer reads (pages touched, split into shared hits versus disk reads), and actual row counts per node. A query that reports 12 ms with 40,000 shared hits is a memory and row-volume problem, while one that reports 800 ms with 3,000 disk reads is a storage problem. The earliest node where actual rows diverge by 10x or more from the estimate is usually where the plan goes wrong, because every join and sort above it inherits that error.
An N+1 pattern makes the arithmetic concrete. An endpoint that fetches a list of 80 items with one query and then one query per item issues 81 round trips; at 1 ms per round trip plus parsing overhead, that is 80+ ms of mostly idle waiting. Batching those 80 lookups into one WHERE id IN (...) or a single join collapses the count to 2 queries and moves the cost to one larger result set, trading 81 network round trips for one response of 80 rows. An index-only scan applies the same logic inside the database: when every requested column lives in the index and the visibility map confirms no concurrent update needs checking, the engine skips heap fetches entirely, often cutting page reads by 5 to 50x on narrow projections.
Caches change which cost dominates, with a freshness trade-off. A cache hit in shared buffers costs on the order of microseconds per page, while a disk read costs milliseconds — a 100 to 1,000x gap — so a working set that fits in memory behaves like a different machine than one that does not. An application cache in front of the database removes the query entirely on a hit, but every cached entry needs a time to live or an invalidation rule, plus memory for the entry itself. Adding indexes follows the same bargain: each index speeds the reads it serves and taxes every insert, update, and delete with an additional write plus ongoing storage, so an index that serves one rare report while taxing a 5,000-writes-per-second table loses the trade.
What the interviewer is listening for
- Measuring buffers, row counts, and time together, and naming the first node where estimates diverge from reality.
- N+1 math for 80 items (81 queries versus 2 batched) and what batching trades away.
- When an index-only scan skips heap access and what the visibility map has to confirm.
- Cache and index trade-offs stated with costs: staleness and memory for caches, write amplification and storage for indexes.
Common mistakes
- Tuning from elapsed time alone without buffer reads and actual row counts.
- Recommending an index for every slow query without accounting for write-path cost.
- Treating a cache as free performance without a freshness and invalidation plan.
- Reading plan cost units as milliseconds instead of the optimizer's relative units.