A slow query got fixed with a rewrite — 8.1x faster, same result set, verified row for row. The more obvious fix, the one that would have addressed the actual physical reason the query was slow in the first place, got rejected. Not out of caution. Because it was measured first, and the measurement said no.
What was actually slow
A query used to build inbound-link summaries pulled data per target using a CROSS JOIN LATERAL with a per-target row limit — one independent index lookup for every target in a batch, up to 500 at a time. The real cost wasn't the lookups themselves; it was that different targets' inbound links often live on the same physical pages of the underlying table, and a per-target lookup re-reads those shared pages once for every target that touches them, with no shared work between lookups that happen to land in the same place.
The rewrite that fixed it
Replacing the per-target loop with a single bulk query — one WHERE ... = ANY(...) clause covering the whole batch at once, with a windowed rank applied afterward to enforce the same per-target cap — let the database choose a scan strategy that reads the table's physical layout once, sorted by location on disk, rather than jumping back and forth per target. Measured directly with EXPLAIN ANALYZE against a real 50-target sample: 245 milliseconds against the old query's 1,995 milliseconds, an identical 254-row result set confirming the rewrite changed nothing about correctness, only cost.
The fix that looked more fundamental
The rewrite addresses the query. It doesn't address the underlying reason different targets' inbound links weren't sitting near each other on disk to begin with — that's a property of how the table's rows are physically ordered, not of any one query against it. The database-level fix for that is a physical reordering operation: rewrite the entire table so its rows are laid out on disk in the same order as one specific index, so future scans following that index touch fewer distinct physical pages. On paper, this looks like the "real" fix, the one that addresses root cause instead of working around it at the query level.
What the actual table looked like before touching anything
Before deciding whether to do that reordering, the table itself got measured: 33GB of table data, 58GB of indexes on top of it, 592.9 million rows. A physical reorder of a table that size requires a full rewrite of the entire table under an exclusive lock — nothing else can read or write to that table while it runs, for however long the rewrite takes, which at this scale is realistically hours, not minutes. This table sits in the write path for every crawler and indexer process across the entire production fleet. An hours-long exclusive lock on it doesn't slow down one query; it stalls every write the whole system needs to make, for the duration.
Why the "more fundamental" fix wasn't actually better
There's a second problem with the physical reorder that has nothing to do with the lock: Postgres doesn't maintain that ordering going forward. New rows inserted after the reorder don't automatically slot into the same physical arrangement — the benefit degrades over time as the table keeps growing and changing, exactly as it does continuously in a live crawl pipeline. Paying an hours-long, fleet-wide write freeze buys a physical layout that starts drifting back toward its previous state the moment normal traffic resumes. The query-level rewrite, by contrast, costs nothing to deploy beyond a normal code change, and its 8.1x improvement doesn't depend on the table's physical layout staying in any particular shape.
The actual decision, not just the outcome
Rejecting the more dramatic-sounding fix wasn't a judgment call made from intuition about which approach felt safer. It was a real comparison: an hours-long, whole-fleet write outage buying a benefit that erodes on its own, against a query rewrite that ships like any other change and solves the same measured problem without touching how the table sits on disk at all. The "obvious" fix loses that comparison as soon as the actual numbers — table size, lock duration, decay of the benefit — are the thing being compared, rather than which fix sounds more like it's addressing the real cause.