Find the Data Access That Dominates Query Cost

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 the actual workload determine whether that reduction matters. The discipline is finding the dominant access pattern first, then optimizing it, rather than indexing everything and hoping.

Count the touches, not the queries

EXPLAIN (ANALYZE, BUFFERS) SELECT id, name FROM customers WHERE email = $1;
 
Index Scan using customers_email_key  (cost=0.42..8.45 rows=1 width=64)
  (actual time=0.09..0.10 rows=1 loops=1)
  Index Cond: (email = 'a@example.com')
  Buffers: shared hit=4
Planning Time: 0.3 ms   Execution Time: 0.21 ms
 
  vs. the same lookup without the index:
Seq Scan on customers  (cost=0.00..15300.00 rows=1 width=64)
  (actual time=4.1..1420.8 rows=1 loops=1)
  Buffers: shared hit=12,800
  Rows Removed by Filter: 199,999
  Execution Time: 1433.9 ms

Same result, 0.21 ms vs 1,434 ms — because the index touches 4 pages where the scan touches 12,800. Page counts are the currency: every optimization should be justified by pages (or rows) removed, and Buffers: in EXPLAIN ANALYZE is the honest ledger. Elapsed time alone can mislead — a "fast" query on a warm cache may be waiting for memory it will not have under load.

An index-only scan takes this further: when every requested column lives in the index and the visibility map says the pages need no heap fetch, the table isn't opened at all. It is the cleanest win available — and the reason covering indexes exist.

The N+1 problem, with arithmetic

The check question: an endpoint issues one query for a list and one query per item. For 80 items that is 81 queries, and the cost is dominated by round trips, not by row counts:

per-query cost (same availability zone, connection pool, small result):
 
  N+1      : 81 queries × (~0.4 ms server time + ~0.3 ms round trip)
            ≈ 81 × 0.7 ms ≈ 57 ms, plus 81 pool checkouts
  batched  : 2 queries (list + WHERE id = ANY($1))
            ≈ 0.9 ms + 1.4 ms ≈ 2.3 ms
 
  57 ms → 2.3 ms: a 25× improvement from changing query count, not
  touching a single index

Cardinality matters too: the batched version returns 80 rows once instead of 80 rows across 80 result sets — same data, a fraction of the protocol and parse overhead. The tell in production is a request whose database time is proportional to list size: queries_per request as a metric catches N+1 the moment it is introduced, because it grows with input where total latency should not.

Cache: measure what it hides

An application cache avoids storage reads — a Redis GET at ~0.3 ms versus a table lookup that needs an index probe — but it buys two costs: staleness and memory.

hit rate math on a read path:
 
  backend latency 4 ms, cache latency 0.3 ms, hit rate 90%
  effective ≈ 0.9 × 0.3 + 0.1 × 4 = 0.67 ms      (6× faster)
  raising hit rate 90% → 98%: 0.98 × 0.3 + 0.02 × 4 = 0.37 ms
  → gains are linear here: each point of hit rate is worth ≈0.037 ms,
    so the first 90 points did the heavy lifting
 
memory: 100,000 entries × 2 KB = 200 MB — plus overhead (Redis objects run
  ~100 bytes each), plus the eviction rate you must measure when it fills

The failure mode is caching the wrong thing: an unbounded per-item cache of 2 KB entries hitting 1M keys is 2 GB — at which point the cache is competing with the buffer pool for the same RAM and making both slower. Bound it (LRU with a size cap), TTL it to your actual freshness contract, and cache the expensive-to-compute values, not the cheap ones.

Costs and failure cases

• Indiscriminate indexes slow writes. Every insert maintains one more B-tree: a table with 5 secondary indexes does roughly 5 index inserts plus page splits on top of the heap write, plus WAL for all of it. Index what you query; measure write latency after each addition.
• Plans change as statistics and distributions change. A query plan that was optimal at 10k rows can be pathological at 10M; revisit plans after major data growth, not only after code changes.
• A fast isolated query still overloads the service under concurrency. 1 ms × 5,000 req/s of sequential scans is 5 cores of scanning; multiply by an N+1 multiplier and you have a connection pool. Load = per-query cost × rate × fan-out — always do the multiplication.

Key takeaways

• Pages and rows touched are the true cost — read Buffers: and row counts next to elapsed time; the index win is 4 pages versus 12,800, not "index good."
• Count queries per request. 81 round trips cost 57 ms where two cost 2.3 ms — batch before you index.
• A cache is a freshness trade plus a memory bill: measure hit rate, bound the memory, and cache what is expensive to recompute.
• Multiply per-query cost by rate and fan-out before declaring anything fast.

PostgreSQL Documentation: Using EXPLAIN covers reading buffers and actual rows.