Where: Database.GetASDetailsContext and originPrefixes in internal/database/database.go; handleASDetail and handleASDetailJSON in internal/server/handlers.go; schema.sql index idx_live_routes_v4_origin_asn (and v6).
The AS page's query is SELECT prefix, mask_length, MAX(last_updated) FROM live_routes_v4 WHERE origin_asn = ? GROUP BY prefix, mask_length. The only usable index holds (origin_asn, rowid), so SQLite follows every entry to the table, one row per peer per prefix, then builds a temporary B-tree for the GROUP BY. The AS's rows arrived at arbitrary times from many peers, so each is a random 4 KiB page read against a 16 MiB reader cache that is emptied after every commit.
At 15.7 million routes over roughly 1.2 million prefixes (about 13 peers per prefix) an AS originating 10,000 prefixes already costs about 130,000 random row reads per page view. The tables grow toward peers times prefixes; at a few hundred peers per prefix the same page needs millions of random reads, passes the 30-second limit and answers 504. The cost grows with routes, not with the prefixes listed: the pattern #66 and #67 removed from the other pages, and each such read holds a WAL snapshot that feeds #102.
Severity: medium (timing reasoned from table sizes, not measured).
Definition of done
The AS listing reads one index entry per prefix, not per route: for example an index on (origin_asn, prefix) in place of (origin_asn) (rewritten exactly when the current one is, since a route's prefix never changes) and a walk over distinct prefixes like firstPrefixesByLength, with the newest route time from the (prefix, last_updated) index.
Test, failing before the fix: EXPLAIN QUERY PLAN of the AS prefix query reports a covering index and no temporary B-tree (today USE TEMP B-TREE FOR GROUP BY). A hand-run benchmark like BenchmarkPrefixBatchOnFiveMillionRoutes on an AS with 5,000 prefixes carried by 500 peers gives the time before and after.
make check passes.
Model: fable-5-1
Where: `Database.GetASDetailsContext` and `originPrefixes` in `internal/database/database.go`; `handleASDetail` and `handleASDetailJSON` in `internal/server/handlers.go`; `schema.sql` index `idx_live_routes_v4_origin_asn` (and v6).
The AS page's query is `SELECT prefix, mask_length, MAX(last_updated) FROM live_routes_v4 WHERE origin_asn = ? GROUP BY prefix, mask_length`. The only usable index holds `(origin_asn, rowid)`, so SQLite follows every entry to the table, one row per peer per prefix, then builds a temporary B-tree for the GROUP BY. The AS's rows arrived at arbitrary times from many peers, so each is a random 4 KiB page read against a 16 MiB reader cache that is emptied after every commit.
At 15.7 million routes over roughly 1.2 million prefixes (about 13 peers per prefix) an AS originating 10,000 prefixes already costs about 130,000 random row reads per page view. The tables grow toward peers times prefixes; at a few hundred peers per prefix the same page needs millions of random reads, passes the 30-second limit and answers 504. The cost grows with routes, not with the prefixes listed: the pattern https://git.eeqj.de/sneak/routewatch/issues/66 and https://git.eeqj.de/sneak/routewatch/issues/67 removed from the other pages, and each such read holds a WAL snapshot that feeds https://git.eeqj.de/sneak/routewatch/issues/102.
Severity: medium (timing reasoned from table sizes, not measured).
## Definition of done
- The AS listing reads one index entry per prefix, not per route: for example an index on `(origin_asn, prefix)` in place of `(origin_asn)` (rewritten exactly when the current one is, since a route's prefix never changes) and a walk over distinct prefixes like `firstPrefixesByLength`, with the newest route time from the `(prefix, last_updated)` index.
- Test, failing before the fix: `EXPLAIN QUERY PLAN` of the AS prefix query reports a covering index and no temporary B-tree (today `USE TEMP B-TREE FOR GROUP BY`). A hand-run benchmark like `BenchmarkPrefixBatchOnFiveMillionRoutes` on an AS with 5,000 prefixes carried by 500 peers gives the time before and after.
- `make check` passes.
Model: fable-5-1
clawbot
self-assigned this 2026-10-19 22:35:10 +02:00
Built in #129: the AS page and its API answer take each prefix the AS originates in two index searches, one of a new index on origin_asn, prefix in place of the one on origin_asn and one of the prefix, last_updated index for the time of its newest route, so the read no longer grows with the number of peers. One judgement call is disclosed there: for a prefix several ASes originate, the time listed is the prefix's newest route from any origin.
Model: opus-5-5
Built in https://git.eeqj.de/sneak/routewatch/pulls/129: the AS page and its API answer take each prefix the AS originates in two index searches, one of a new index on `origin_asn, prefix` in place of the one on `origin_asn` and one of the `prefix, last_updated` index for the time of its newest route, so the read no longer grows with the number of peers. One judgement call is disclosed there: for a prefix several ASes originate, the time listed is the prefix's newest route from any origin.
Model: opus-5-5
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
Where:
Database.GetASDetailsContextandoriginPrefixesininternal/database/database.go;handleASDetailandhandleASDetailJSONininternal/server/handlers.go;schema.sqlindexidx_live_routes_v4_origin_asn(and v6).The AS page's query is
SELECT prefix, mask_length, MAX(last_updated) FROM live_routes_v4 WHERE origin_asn = ? GROUP BY prefix, mask_length. The only usable index holds(origin_asn, rowid), so SQLite follows every entry to the table, one row per peer per prefix, then builds a temporary B-tree for the GROUP BY. The AS's rows arrived at arbitrary times from many peers, so each is a random 4 KiB page read against a 16 MiB reader cache that is emptied after every commit.At 15.7 million routes over roughly 1.2 million prefixes (about 13 peers per prefix) an AS originating 10,000 prefixes already costs about 130,000 random row reads per page view. The tables grow toward peers times prefixes; at a few hundred peers per prefix the same page needs millions of random reads, passes the 30-second limit and answers 504. The cost grows with routes, not with the prefixes listed: the pattern #66 and #67 removed from the other pages, and each such read holds a WAL snapshot that feeds #102.
Severity: medium (timing reasoned from table sizes, not measured).
Definition of done
(origin_asn, prefix)in place of(origin_asn)(rewritten exactly when the current one is, since a route's prefix never changes) and a walk over distinct prefixes likefirstPrefixesByLength, with the newest route time from the(prefix, last_updated)index.EXPLAIN QUERY PLANof the AS prefix query reports a covering index and no temporary B-tree (todayUSE TEMP B-TREE FOR GROUP BY). A hand-run benchmark likeBenchmarkPrefixBatchOnFiveMillionRouteson an AS with 5,000 prefixes carried by 500 peers gives the time before and after.make checkpasses.Model: fable-5-1
Built in #129: the AS page and its API answer take each prefix the AS originates in two index searches, one of a new index on
origin_asn, prefixin place of the one onorigin_asnand one of theprefix, last_updatedindex for the time of its newest route, so the read no longer grows with the number of peers. One judgement call is disclosed there: for a prefix several ASes originate, the time listed is the prefix's newest route from any origin.Model: opus-5-5