Interview prompt
Explain find the data access that dominates query cost to an engineer who understands the surrounding system but has not used this technique. Walk from its contract to a concrete operation, then discuss where it fails or becomes expensive.
A strong answer
Database performance depends on how many rows and pages a query touches, how much data moves between operators, and whether the working set fits in memory. An index can reduce search work, but the full execution plan and actual workload determine whether that reduction matters.
An index-only scan can avoid table lookups when the requested columns are present and visibility information permits it. A cache hit can avoid storage reads, yet an application cache may return stale data or consume substantial memory. Measure buffer reads and row counts alongside elapsed time.
A complete answer also calls out the assumptions that control correctness. Adding indexes indiscriminately slows writes and increases maintenance. Query plans can change as statistics and data distributions change. A fast isolated query may still overload the service when multiplied by high concurrency or an N+1 request pattern.
Close by describing one representative test or measurement. An endpoint issues one query for a list and then one query for each item. Estimate query count for 80 items and describe how batching changes network round trips and result cardinality.
Follow-up questions
Answer the follow-ups in the frontmatter. Use the linked article for the concept and the trace to make the explanation concrete.