/prerender_admin/pages 504s on any prefix with no match at the seek point — the two-sided PK range does not prune the scan

[harper-joseph]

Summary

GET /prerender_admin/pages?prefix=… returns 504 {"error":"page-cache read timed out"} whenever the
prefix has no matching row sitting at the seek point. The two-sided primary-key range built in
listPagesInner does not prune the walk — the scan continues past the less_than bound until it
either finds limit + 1 matches or reaches the end of the table.

The cost is in the scan, not the rows: the same prefix costs the same at limit=1 as at
limit=30, and a prefix matching zero rows still times out.

Evidence

Measured against a large PrerenderedPage table (~800k keys), limit=1 unless noted:

probe	result
prefix matching rows at the seek point	200, rows returned
facet-style prefix, no match at seek (…/catalog.jsp?CN=Size:-shaped)	504
product-style prefix, no match (…/product/prd-QQQ-no-match-shaped)	504
prefix sorting after every key	200, 0 rows (fast — hits EOF immediately)
prefix sorting before every key, no match	504 (walks the whole table)
no prefix at all	200, fast (one-sided walk bounded by limit)
same failing prefix at limit=1 / 5 / 30	504 / 504 / 504 — cost is flat in limit

The position in the key space doesn't matter; what matters is whether a match is found immediately.
Long facet-style prefixes just fail most often, because an exact match rarely sits at the seek point.

Cause

src/resources/PrerenderAdmin.js, listPagesInner:

const conditions = [{ attribute: 'cacheKey', comparator: 'greater_than', value: cursor || prefix || '' }];
if (prefix) {
    conditions.push({ attribute: 'cacheKey', comparator: 'less_than', value: `${prefix}�` });
}

The less_than bound is evidently not terminating the iteration, so a non-matching region is walked
to EOF. READ_TIMEOUT_MS (3000) then trips and we return 504.

Proposed fix

Drop the less_than condition and bound the page client-side. Results are already
sort: { attribute: 'cacheKey' }, so the first key that does not start with prefix ends the
page — no second condition needed:

const conditions = [{ attribute: 'cacheKey', comparator: 'greater_than', value: cursor || prefix || '' }];
// …then stop consuming at the first row whose cacheKey does not start with `prefix`

That makes a zero-match prefix cost one seek plus one row, and matches the bounded-walk /
keyset-pagination pattern already used elsewhere in this file rather than trusting a two-sided PK
range.

Worth adding a regression test for the zero-match prefix case specifically — it is the case that
fails, and it is invisible to any test whose fixture happens to contain a matching key.

Two related observations

1. A successful query still has a ~3.3s floor, which is poor for a console browse click. That is
   not the page read (a limit=1 hit can return in ~0.2s) — it is the surrounding work:
   withHeavySlot, the invalidations read, and the backlog-snapshot read. Worth profiling
   separately.

2. The 504s come back at roughly 2× the 3s budget (measured 6.1–7.5s, consistently). A 3000ms
   timer that fires at ~6s could not fire on schedule, which suggests the native range walk is
   blocking the event loop rather than yielding. If so, an operator browsing the console can
   stall unrelated requests on that worker for seconds — MAX_CONCURRENT_HEAVY = 2 bounds it to two
   workers, which is likely why it has gone unnoticed. Not proven; confirming needs an event-loop-lag
   measurement taken while a slow prefix runs. If it holds, it is the more important half of this
   issue, since the serve path shares the worker pool.

Note the serve path itself is unaffected: serving does a point read by cacheKey and never builds
this range. The slow query is reachable only through the super_user admin API.