Our trending-players query was crossing a line we didn't know we'd drawn: the 5-second statement timeout. NBA trending took about 5.7 seconds on prod, so the stats home page timed out more than it loaded. The cause wasn't a missing index or a slow disk. It was a perfectly reasonable-looking semi-join.
What a semi-join does
A semi-join filters one table by membership in another: "give me the rows that have a match." SQL has no explicit SEMI JOIN keyword. You write it as EXISTS (...) or IN (subquery), and the planner is free to run it however it wants. Ours chose a nested loop with index probes. Each probe is cheap. Nine million of them, less so.
The fix: aggregate once, then join
The interesting part is what we didn't do. No new index, no timeout raise, no caching layer. The whole fix is a handful of lines in one file: the prior-season fallback used to restrict its rows to current candidates with a semi-join, and now it aggregates the prior season once, then joins that small aggregated result to the candidates. The planner goes from millions of probes to one aggregation and a small join. Same top 5 players, about 250 ms instead of 5.7 s. That's a 23x speedup from nine lines, because the change moved the filtering from inside a loop to before the loop.
The same night, in the same product
The query fix was one lane of a bigger push. The theme across all of them is the same: small, surgical changes with outsized impact.
- Refetch errors keep content: if a refresh fails on mobile, the screen now keeps the last good data instead of blanking. Stale stats beat no stats, almost always.
- Stalled requests time out: requests that hang now fail at a fixed deadline with a typed error, so the app can retry instead of spinning forever.
- Empty weeks stay usable: an empty schedule scope shows an empty board rather than an error, and a player traded mid-week keeps the later game's row instead of failing the whole board.
- Ask questions ranked by demand: the question catalog now scores question families against real search demand, and a generator produces player-vs-team and record questions for the next seven days of games, dry-run first.
- The game-sync queue un-clogs itself: messages whose send never made it out are released for the next pass instead of getting lost, and poison messages reach the dead-letter queue in seconds instead of blocking everything behind them.
The first two are worth a second look, because they're a product stance as much as an engineering one. The alternative to keeping stale content is a spinner, then an error screen that replaces good data with nothing. Showing slightly old numbers is almost always better. It just has to be deliberate.
Ship the measurement, not the theory
The 23x didn't come from a clever idea. It came from numbers: 5.7 seconds before, 250 milliseconds after, same top 5 players both times. The query was "correct" in both versions, and reading it would never have revealed the probe count. The timeout pointed at the page, the page pointed at the query, and someone had to go look. If your slow page has a timeout nearby, follow the chain yourself. The fix might be nine lines.