The query wasn't on a hot path, which is exactly why it got away with it. A nightly report ran a join across an events table and a lookup table, aggregated the lot, and emailed someone a number. It took a few seconds when we wrote it. By the time anyone noticed, it was taking eleven minutes and holding a long read transaction open the whole while, which started to annoy everything else trying to use the same tables at 3am.
I caught it the dull way: the report job started overlapping with the next morning's batch and the two stepped on each other. EXPLAIN ANALYZE on the query showed the events table being scanned in full, then sorted, then joined, with the actual row count an order of magnitude past the planner's estimate. The table had simply grown, and the join column had no index that matched the filter and the join together.
The fix was a covering index, one that carried the columns the query selected so the database never had to go back to the heap:
CREATE INDEX idx_events_day_type
ON events (event_day, event_type)
INCLUDE (user_id, amount);
With that in place the planner switched to an index-only scan, the full table read vanished, and the report dropped from eleven minutes to under four seconds. The long-running transaction stopped blocking the morning batch as a free consequence.
The thing I want to remember is that nothing alerted on this. It degraded smoothly from fine to awful over months, and the only reason it surfaced was a scheduling collision, not a monitor. A slow query that no human waits on is the easiest kind to ignore and the easiest kind to fix once you finally look. So the report job now logs its own runtime and complains if it crosses a minute. Cheap insurance against the next slow creep.