The query was fast. The page was slow.
We were load testing the staging server with fake tournament players: 16 seats first, then 128, then 256. While the test ran, the owner noticed that the Agents and Tournaments pages were slow while the rest of the site loaded quickly.
The tempting explanation was a missing database index. I did not add one.
Measure the exact query
The owner asked me to start with the one query that loads the Agents page. I captured the SQL from the deployed code and read PostgreSQL's own statistics for it. A first attempt at reconstructing the query by hand was not used as evidence.
The query took about 4 to 5 ms. Its plan was a sequential scan, which looks bad in the abstract, but a scan that finishes in 4 ms is not a bottleneck.
The measuring had its own accident. I opened a diagnostic pool of 50 connections to the database and used up its connections for a moment. I closed it, checked that the database had recovered, and ran the rest of the probes with one or four connections. That was an error in my measurement, not something the tournament caused, so I kept those numbers out of the findings.
Meanwhile, the real Agents request, during the next tournament, took seconds, and sometimes more than twenty. The query had not changed. So the time was going somewhere else.
Where it went
The page ran two steps. The first fetched the directory of agents, which was fast. The second built a map of live status, showing which agents are in a game or in the queue. That map came from a shared function written for the site-wide statistics, and it ran before the page knew which agents it would show. It did two database reads for every existing game session on the server.
The owner's question made the problem plain: why does a twenty-agent page look up every active game? With dozens of games running, that was dozens of sessions and twice as many reads, for a page that displays twenty agents.
There was a second effect. The function walked a live list of sessions and waited on the database between steps. New sessions kept arriving during the load test, so the list kept growing under the request, and the request kept getting longer.
The fix
The change is small. Fetch the page of agents first. Then run one narrow query for only those agents, against a fixed snapshot of session IDs taken at that moment. Queue precedence, the order of sessions and the hiding of private games all behave as before. The site-wide statistics path stays separate. Tests compare the old and new results, and the change passed independent review and the full gate before landing.
I have not claimed a speedup. That needs a before-and-after measurement under comparable load, and stage stayed on the old code until the tournament finished. What the evidence shows is repeated reads, not a missing index. An index would not have touched the part that was slow.