Indexes & Query Plans

This cover names the uncomfortable truth: the bottleneck in most applications isn't the application code, it's a single database query reading far more data than it should. And the table that query hits grew quietly over months while everyone shipped features and nobody watched the row count.

Post two is about stakes. Post one explained the machinery; this post explains why you should care — the failure modes, the costs, and how a problem invisible at launch becomes an emergency at scale.

The defining property of a full table scan is that its cost grows with the size of the table, not the size of the result. A query returning one row costs the same as one returning a million if both must scan the whole table. At 1,000 rows that scan is imperceptible; at 10 million it's seconds per request.

This is why slow databases feel like they appear from nowhere. The code is unchanged, the query is unchanged, but the data crossed a threshold where the scan stopped being free. Teams that don't understand this blame the framework, the language, or the cloud — when the real cause is a missing index on a now-large table.

The bar chart makes the scaling visceral. At a thousand rows the full scan is essentially instant. At a hundred thousand it's noticeable but tolerable. At ten million it crosses into timeout territory. The query never changed — only the row count did.

The lesson is to think in terms of growth, not the present. A query that's fine in development against a seeded table of a few thousand rows can be a production incident waiting to happen. Test query plans against realistic data volumes, because the plan that's optimal at small scale can be catastrophic at large scale.

This slide quantifies what a full scan actually wastes. The engine reads every page of the table from disk into memory, examines each row against your filter, and discards the non-matches. That's I/O bandwidth spent reading data you don't want, CPU spent comparing it, and — often worst — cache pollution.

The cache point is subtle but important. Databases keep hot data in memory; a giant scan can evict that hot data to make room for pages it only needs once, slowing down every other query that relied on the cache. So one bad scan doesn't just hurt itself — it degrades the whole system's working set.

Here the cost becomes systemic. A slow query holds its database connection for the entire time it runs, and may hold locks too. Connections are a finite pool. Under normal load there's slack, but during a traffic spike, slow queries occupy connections long enough that the pool empties.

Once the pool is drained, even fast, well-indexed queries can't get a connection — they queue, then time out. The application starts failing requests that have nothing to do with the slow query. This is how a single un-indexed lookup escalates into a full outage: not by failing itself, but by starving everything else of resources.

The flow diagram traces that escalation as a chain so you can recognize it in an incident. It starts with a missing index causing a full scan, which slows the query, which holds a connection, which drains the pool, which makes requests queue and time out, which is an outage.

The value of seeing it as a chain is that you can break it at any link — but the cheapest and most durable fix is the first link. Add the index, and the scan never happens, so the connection isn't held, so the pool stays healthy. Fixing downstream symptoms (bigger pool, more servers) treats the cascade without removing its cause.

This slide introduces a more subtle failure: the planner itself making a bad choice. The planner doesn't know your data directly; it works from statistics — estimated row counts and value distributions it samples periodically. When those statistics are stale (a bulk load happened) or skewed (one value dominates), its cost estimates are wrong.

A wrong estimate leads to a wrong plan: it might choose a sequential scan believing the index would match too many rows, or pick a nested loop join that's disastrous on a large input. Knowing the planner is an estimator, not an oracle, is what lets you diagnose 'I have the index, why is it slow?' situations.

This example puts a number on the stakes. The same query against ten million orders takes 4.2 seconds as a full scan and 21 milliseconds after a single index — roughly a two-hundred-fold improvement from one line of DDL. No code change, no bigger server, no caching layer.

The planning-time note is also instructive: planning is sub-millisecond either way. The cost is entirely in execution, and execution cost is dominated by how much data the chosen plan reads. This is the leverage indexes give you — enormous execution wins for trivial effort, if you know where to apply them.

This comparison lays out the business consequences side by side. A well-indexed database delivers predictable latency as data grows, uses little CPU and I/O per query, has headroom to absorb spikes, and can run on smaller, cheaper instances. An un-indexed one gets slower with growth, thrashes I/O and cache, cascades into timeouts under load, and pushes you toward ever-bigger servers.

Framed this way, indexing isn't a niche optimization — it's a cost and reliability lever. The same workload can cost a fraction as much and stay up under load purely because the queries read the right amount of data.

This is the most common wrong reaction, called out directly. When the database is slow, the reflex is to scale the instance up — more CPU, more RAM. It helps a little, because a bigger cache hides some I/O, and it costs a lot. But a full scan of ten million rows is still a full scan; you've made the wrong work faster, not eliminated it.

The right move is almost always to find the missing index or rewrite the offending query. It's cheaper, the win is larger, and it actually removes the bad work instead of masking it. Reach for hardware only after you've confirmed the query plans are already good.

This recap compresses the stakes into five lines: slow queries scale with data, full scans waste I/O and CPU, one bad query can take down everything, stale statistics cause bad plans, and indexing beats upsizing servers. Each is a real failure mode you'll eventually meet in production.

The through-line is that performance is a reliability and cost concern, not a vanity metric. Understanding why these things matter is what motivates learning the mechanics in the next post.

Post two made the case for caring. Post three opens the hood: the full journey from SQL text to returned rows, how a B-tree seek actually executes, and how the planner costs and chooses among competing plans.