The page editor was stalling. Not slow, stalling: you’d open Stat Leaders for a league’s LA page and wait past the point where you’d normally assume something had crashed.
I started where you’d expect: network tab. Nothing unusual, request just sat there. Next guess was the query itself, maybe an aggregate over a season’s worth of games doing something expensive in application code before it ever touched the database. I went through the stat-leaders assembly path looking for an N+1, a loop that fetched play-by-play rows one game at a time and summed them in Python instead of SQL. Didn’t find one. The aggregation was already a single query against the games table, filtered by league and season.
So I ran that query directly against the database instead of through the app. Same stall. That ruled out the application layer entirely, which meant I’d spent time chasing a bug in the wrong layer before I had evidence it was there. The query itself was the problem, not what wrapped it.
With the app out of the picture, I pulled the plan for the query. It was a full table scan on games, filtered on league and season, with no index covering either column. Every Stat Leaders load was walking every row in the table, checking each one against the filter, for a table that had been accumulating games across every league, every season, for years. The bigger the table got, the worse the page got, and nobody had connected the two because the growth was gradual and the page had probably never been fast to begin with.
The fix was one index:
CREATE INDEX idx_games_league_season ON games(league_id, season_id);Composite, league first since that’s the higher-selectivity filter for this workload, season second. After the index, the same query against the same table went from a stall to under a second, and the LA page editor round-trip landed at about 4 seconds end to end, most of which is render, not query time.
What made this one worth writing up isn’t the fix, it’s that I burned real time on the wrong hypothesis first. A stalling page editor looks like a frontend problem or a network problem, especially when you’re used to those being the usual suspects. I checked both before I checked the one thing that actually explains why a table scan gets worse over time and a slow query in application code usually doesn’t: it degrades silently as data grows, with no error, no timeout, no signal except “this used to be fine and now it isn’t.” Nobody filed a ticket saying “the games table lacks an index,” because from the outside all you can observe is a page that takes forever, and that symptom maps to a dozen different causes before it maps to the right one.
I closed the follow-up ticket, cleaned up the customer’s open ticket with a note for the support lead to send back, and filed the transaction-log backup gap I’d noticed while I was in there as its own separate ticket rather than trying to fix it in the same pass. That’s a different problem with a different blast radius, and bundling it in would have meant re-testing a change that had nothing to do with the one I’d just verified.
The diagnostic order I’d use next time: reproduce against the database directly before touching application code. It’s the cheapest way to rule out an entire layer, and it would have saved the detour through the stat-leaders assembly path. Full table scans don’t announce themselves. They just make a page a little worse every month until “a little worse” turns into “doesn’t finish loading,” and by then the only way back is EXPLAIN on the actual query, not a guess about where the time is going.