My search engine has an admin page, /admin/host-space, where I can mark a whole host for eviction: every URL from that host gets dropped from the corpus on the next eviction cycle. I shipped it on 2026-09-13. Within minutes of the deploy, the shared database server was running multi-minute full scans of the 43-million-row url table, over and over, to find the URLs of exactly zero hosts.
The query that looked harmless
The eviction command builds its candidate list with a UNION of a few criteria. The new branch for admin-requested host eviction was the obvious one:
SELECT u.id FROM url u JOIN host h ON h.id = u.host_idWHERE h.eviction_requested_at IS NOT NULL AND h.eviction_completed_at IS NULL
Both columns were brand new, added by the same deploy. Nobody had pressed the button yet, so the real number of matching hosts was 0. The query is correct. Run against a planner with accurate statistics, it would find nothing in a few milliseconds.
What the planner saw instead
Postgres had never run ANALYZE on the new columns, so it had no statistics for them and fell back to its default selectivity guesses. It estimated about 22,000 matching hosts. With that many hosts on one side of the join, the cheapest-looking plan was a Parallel Index Only Scan across the entire url table, hash-joined against the host list.
That plan isn't wrong for 22,000 hosts. It's a disaster for zero. Every eviction cycle started the same scan, and each run took several minutes on the dbserver that the crawler, the indexer and PageRank all share.
The frustrating part is that the command fixes its own statistics. Further down the same file, updateGrowthThrottle() runs an unconditional ANALYZE host. Once that ran, the estimate would have dropped to something sane and the planner would have chosen differently. But the runs weren't surviving long enough to reach it. The bad plan kept starting before the line that would have corrected it.
Two mitigations that weren't allowed to happen
I was working through this with an AI agent, and its first two moves were the ones I'd have reached for myself: cancel the stuck queries, then run ANALYZE host by hand so the next cycle would plan correctly. Both were refused by the environment's own guardrails, because they were direct writes against a production database and that session wasn't authorized for them.
In the moment that felt like friction. In hindsight it was the right call, and it changed what got fixed. A manual ANALYZE would have made this incident go away and left the query exactly as fragile as before: the next time a fresh column met a stale planner, the same plan would come back. Without that shortcut, the fix had to go into the code, through the normal deploy path, where it stays.
The fix: give the bad estimate nothing to multiply
The rewrite, in commit 1f32ef2, splits the query in two. First, resolve the pending hosts from host alone:
SELECT id FROM host WHERE eviction_requested_at IS NOT NULL AND eviction_completed_at IS NULL
That query can still be badly estimated, but it doesn't matter. It touches one table, bounded by that table's own partial index, and there's no second table for a bad row estimate to blow up into a bad join. It returns the real list, which is usually empty.
Then, only if the list isn't empty, fold the concrete ids into url:
SELECT id FROM url WHERE host_id IN (...)
The planner sizes that predicate from url's long-established host_id statistics, not from the new columns. When the list is empty, the branch isn't added to the UNION at all. The breakdown query that counts how many URLs each criterion contributed got the same treatment, and short-circuits to 0 when there are no pending hosts.
All 39 existing eviction tests passed unchanged, which tells you something on its own: the tests checked what the query returned, and the old query always returned the right thing. Nothing in the test suite could see that it took minutes to return it.
What I took from it
The general lesson is about new columns on hot paths. A column added by a migration has no statistics until something analyzes it, and the planner's defaults for "IS NOT NULL on a column I know nothing about" can be wildly off. That's fine for an admin page someone clicks twice a month. It isn't fine for a background criterion that runs every cycle and joins against the biggest table in the database.
The specific lesson is the shape of the fix. Resolving a small, selective set of ids first and then using them as literals is a pattern I'd been treating as a micro-optimization. Here it was what made the query safe no matter what the statistics said. The code comment above the new version records the incident, so the next person who sees an apparently redundant two-step query and wants to fold it back into a join will know why it isn't one.
Leave a comment
Comments are reviewed before they appear.