SQL Tuning

Copies removed one join, but one login still scans eight million rows

Denormalization ended with copies removing joins from the hot read at a price: 40 extra bytes per row, roughly 4 GB per 100 million rows, plus a drift detector and a nightly reconciler for every copied field. Copies help only the read path you copied for. Login takes four seconds, but only often enough that support tickets pile up. The query looks harmless: find one user by email. Without a sorted shortcut the database reads all eight million rows one by one for every login, and while each request waits it holds a database connection, which is a server-side slot serving one client. Connections run out long before the processor does, so one slow query queues everyone behind it.

The naive fix is more hardware or more connections, and it fails because the work per login never shrinks. A second tempting fix is indexing every column so no query ever scans, and it fails on the write side with numbers: twenty indexes on a users table turn each insert into twenty maintenance writes plus the heap write, so a write-heavy table doing 5,000 inserts a second pays 100,000 index writes a second and disk grows past the table itself. A faster box scans eight million rows slightly faster, and more connections just let more requests wait simultaneously. The work itself must drop.

Tuning, which means changing how the database finds rows rather than how much hardware it has, fixes the work first. A single missing sorted shortcut routinely turns a 900-millisecond scan into a 1-millisecond lookup, which is a 900-fold improvement in an afternoon. Think of a mailroom that keeps a sorted recipient list instead of opening every parcel to find one name: one analogy for the whole idea, where the index is the sorted list and the table is the pile of parcels.

A sorted shortcut turns eight million comparisons into a few page hops

An index, which is a separately stored sorted structure the database maintains automatically on every write, points straight at the rows matching a value. The database pays for the shortcut on every insert and update, which is why indexes are chosen deliberately rather than sprinkled. The arithmetic shows the stakes: 8,000,000 row comparisons at roughly 900 milliseconds versus three levels of tree descent plus one row fetch at roughly 1 millisecond for the same login query.

Selective, queried columns earn indexes while tiny tables do not

Selectivity, which is how narrowly a value picks out rows, decides value. An email column with millions of distinct values is highly selective, so its index eliminates nearly everything. A boolean status with two values is unselective, so its index still leaves half the table to scan. Foreign keys, which are columns pointing at rows in another table, earn indexes because joins filter on them constantly, while tiny tables under hundreds of rows are already instant to scan and gain nothing.

One composite index serves a whole query pattern when columns are ordered

A frequent pattern like my orders newest first filters two columns at once: the owner and the timestamp. A composite index on both beats two single indexes because the engine descends once and finds rows already ordered. Order matters because the structure works left to right, like a filing system sorted by surname then first name: it answers queries on the first column alone or the first two together, but never the second column alone.

Five query habits remove whole categories of waste

Each habit targets a distinct mechanical waste: planning blind, moving unneeded bytes, scanning unbounded lists, blinding the index with functions, or multiplying round trips inside loops. Together they cover most slow-query incidents before any schema change.

Four query smells point at the plan before touching the schema

Each smell has a mechanical reason. A leading-wildcard search cannot descend a sorted structure because the starting characters are unknown, so it scans. An OR across different columns often blocks index use because the engine would need two descents merged, where an explicit union of two indexed queries descends each cleanly. A list of thousands of identifiers bloats planning and transfer, where a temporary table joined once stays compact. A fresh connection per request pays handshake latency the pool would have amortized.

Read the execution plan using thresholds that trigger action

The login story in plan form is a sequential scan over 8 million rows with nearly all rows removed by the filter, costing 900 milliseconds, versus an index scan touching three tree levels plus one heap fetch in about 1 millisecond, where the heap is the storage holding row data. Three plan signals trigger action on most teams: any hot-path query over roughly 100 milliseconds, any sequential scan over roughly 10,000 rows in production, and any on-disk sort spilling to temporary files. Setting the slow-query threshold to log statements over 200 to 500 milliseconds surfaces offenders weekly, and tuning those before anything else compounds because hot paths run most often.

Loops, wide rows, and deep offsets multiply work the plan cannot hide

The orders page loads 50 orders with one query then loops to fetch each customer separately, which is 51 round trips paying network latency plus planning overhead each. At 2 milliseconds apiece that is 100 milliseconds of waste before any real work, and the pool saturates under load while accomplishing nothing. The fix is one batched fetch with a join or a single identifier list, stitched in memory. The same multiplier logic condemns fetching a 2 MB description for list views, which multiplies payload a hundredfold, and deep offsets, where skipping 100,000 rows still reads and discards 100,000 rows. Keyset pagination, which filters after the last seen key and limits the page, keeps scrolled lists constant-time.

Pools and covering structures finish what single indexes start

The fastest query still waits when no connection is free, so pool aggressively with tens of real backends per database and hundreds of logical connections through a pooler, which is a proxy multiplexing many clients onto few server connections, plus statement timeouts so one runaway cannot drain the pool. On the index side, a covering index, which includes every column the query needs, answers from the shortcut alone in an index-only scan without touching the heap. A partial index goes further by indexing only the hot slice, such as active orders or recent events, so the structure stays small while covering the 5% of rows everyone queries. Unused indexes get audited through usage statistics and dropped, because each one taxes writes while helping nothing.

Fast queries on one box carry a system shockingly far: covering indexes answer from the shortcut alone, partial indexes cover the hot 5%, and the slow-query log over 200 to 500 milliseconds surfaces offenders weekly. Tuning assumes the question has a key to descend on. Some data arrives with no key in hand at all, such as a million sessions, carts, and flags fetched by identifier millions of times a minute, where firing up a parser and planner for give me the thing labeled X is wasted work. Skipping the machinery entirely is the key-value contract.