#1008 · stacks/wildloop

Symptom

Sorting the catalog by Name gets slower the further you page, and past roughly offset 100,000 it crosses the edge timeout and returns HTTP 520 (Cloudflare, origin failed) instead of results.

It is a timeout race rather than a hard cutoff — the same offset can succeed or 520 depending on how the request lands.

sort=name  offset=60,000    200/6.7s   200/7.5s   200/6.6s
sort=name  offset=120,000   200/9.0s   520/10.2s  200/9.3s
sort=name  offset=300,000   520/10.4s  520/12.0s  520/12.7s

Every other sort is fine at the same offset:

offset=60,000   sort=distance   200/0.94s
offset=60,000   sort=longest    200/0.47s
offset=60,000   sort=rating     200/1.44s
offset=60,000   sort=name       200/6.7s      <- 7 to 14x slower

Cause

There is no index on trails.name.

SELECT name, sql FROM sqlite_master WHERE type='index' AND tbl_name='trails';

returns trails_trails_country_state_name_index on ("country", "state", "state_name") — that is state_name, a different column. Nothing covers name.

So ORDER BY name over 596,556 rows sorts the whole table before it can skip to the offset, and the cost grows with depth. The sorts that stay fast are the ones with an index behind them: trails_trails_distance_index for distance and longest, trails_trails_national_trail_index for the default.

Impact

The Name chip on /trails is broken for anyone who pages deep, and it fails as a blank error rather than a slow list. It also makes sort=name unusable for walking the catalog programmatically, which is how the sampling in #1002 had to be done.

Low user impact today — few people page 100,000 trails deep by name — but it is a whole-table sort on every Name request, so even the shallow ones cost seconds of origin CPU.

Fix

CREATE INDEX IF NOT EXISTS "trails_name_index" ON "trails" ("name");

Worth confirming it is actually used before closing, since SQLite may still prefer a scan depending on the surrounding filters. Deep OFFSET is linear regardless, so if Name paging needs to work at any depth, keyset pagination (WHERE name > :last) is the real answer and the index is what makes it possible.

Method

Live production API, 30 Sep 2026, three runs per offset with limit=50. Index list read from the schema in this repository.

Found while sampling the catalog for #1002.

Confirmed the planner actually uses it

The issue said this needed checking before closing, since an index is only a fix if the planner picks it — and a planner picks by cost, so testing on a handful of rows would have proved nothing.

The tests build 20,000 rows with names deliberately out of insertion order, run ANALYZE so the planner has statistics like production does, and assert the plan rather than a wall-clock time that would be flaky under load:

without the index: USE TEMP B-TREE FOR ORDER BY — the whole-table sort this issue is about

with it: trails_name_index, and no temp b-tree, at offsets 0 / 60,000 / 120,000 / 300,000

same rows returned either way, so the index changes the route and not the answer

Measured separately at 200,000 rows, offset 120,000, five runs:

no index 137.2 ms with index 0.6 ms

Production carries 596,556 rows, so the unindexed cost there is worse than that.

What this does not fix

Deep OFFSET is still linear. The engine walks past every skipped row — it just walks an index now instead of sorting the table first. Paging to the far end of the catalog by name remains real work, and keyset paging (WHERE name > :last) is the answer if it ever needs to be cheap. There is a test asserting the plan still contains a SCAN, so this does not get remembered as more fixed than it is.