A query rewrite that shipped an 8.1x speedup still had one unbounded edge case left in it. Finding the actual fix took trying two more reasonable-sounding optimizations first, measuring both, and watching both make things worse.
The edge the earlier fix didn't cover
A previous rewrite replaced a slow per-target query pattern with a single bulk query, ranking each target's inbound links with a windowing function and keeping only the top results per target. That fix was real and correctly measured — a clear win on typical data. What it didn't fully account for is that a windowing function still has to sort an entire group before it can discard anything past the cutoff, no matter how large that group actually is. For an ordinary target with a normal number of inbound links, that's cheap. For one specific page — a cookie-consent widget page that nearly every crawled site on the internet happens to link to — it wasn't.
How large "nearly every site links to it" actually is
That one page had 159,143 inbound links pointing at it from across the corpus. Included in a batch alongside ordinary targets, the bulk query still had to fully rank-sort all 159,143 rows for that single target before it could keep just the handful actually needed. Measured directly: 63.5 seconds spent on that one target alone, inside a query meant to process an entire batch efficiently.
The first thing tried, and why it made things worse
The natural next idea was to defer the more expensive parts of the query — joining in additional URL and text data — until after the ranking filter had already cut the result set down to just the winners, on the theory that filtering first and enriching second should always be cheaper than doing both together. Tried and measured directly: 94 seconds, slower than the original. The database's own query planner had been choosing a parallelized scan strategy for the original shape; restructuring the query to defer the joins broke the specific pattern the planner needed to recognize in order to parallelize the work, and the loss of parallelism cost more than the deferred joins saved.
The second thing tried, and why it didn't help either
The next idea was to drop the arbitrary ordering inside the windowing function itself, on the theory that an unordered ranking might let the database avoid a full sort. Tried and measured: no improvement, still well over 100 seconds. The actual cost wasn't the sort's comparison function — it was the windowing function's fundamental requirement to see the whole group before it can rank any of it, regardless of what ordering, or lack of one, gets applied to that ranking.
What the measurements were actually pointing at
Both rejected attempts targeted the wrong part of the query. The windowing function itself — not the joins around it, not the specific ordering inside it — is what forces the database to materialize an entire group before it can return anything. No amount of restructuring the surrounding query changes that fundamental property. The two "obvious" optimizations both left the windowing function itself untouched and predictably didn't fix the one problem actually causing the slowdown.
The fix that came from actually accepting that constraint
Rather than trying to make the windowing approach faster for an extreme case it was never well-suited to, the query got split by a simple, already-available signal: how many inbound links a target already has on record. Ordinary targets keep using the existing bulk-ranking query, untouched, since it's genuinely efficient for them. The rare handful of targets that cross a size threshold — only 2,118 URLs corpus-wide, out of a corpus of tens of millions — get routed through a separate, per-target query using a plain result limit with no ranking function at all. A plain limit lets the database stop scanning as soon as it has enough rows, regardless of how many total rows exist behind that point — the exact property a windowing function can never offer, no matter how the query around it is arranged.
Why the two failed attempts were worth trying anyway
Neither rejected idea wasted the time it took to try, because both were genuinely plausible before being measured, and both measurements ruled out an entire category of fix — restructuring the query around the windowing function — rather than leaving that possibility as untested guesswork. Finding the actual fix required knowing, with real numbers, that the problem wasn't the joins and wasn't the ordering, which only became certain once both had been tried and both had failed to move the number in the right direction.
Leave a comment
Comments are reviewed before they appear.