The model
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.
A concrete walk-through
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.
Costs and failure cases
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.
Check your understanding
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.