ListKnownRoutes takes 60s+ for rare IATAs (routes?iata=GPT -> 502)

[MrAlders0n]

Symptom. GET /api/v1/routes?iata=GPT&limit=50 took 1 minute and returned 500 on analyzer.meshmapper.net (2026-09-02 02:39 UTC); Apache gave up at its 60 s proxy timeout and the browser saw 502 (23 times). Other IATAs answer normally.

Likely cause. ListKnownRoutes (db/queries/queries.sql:1111-1118):

WHERE ($1 = '' OR iata = $1)
  AND ($2 = 0 OR hop_count = $2)
  AND ($3::timestamptz IS NULL OR last_seen < $3)
ORDER BY last_seen DESC
LIMIT $4;

The ($1 = '' OR iata = $1) pattern hides the selectivity of iata from the planner, which then walks idx_known_routes_last_seen backwards looking for 50 matching rows. For a rare IATA that is a scan of nearly the whole table. The existing indexes are (iata), (iata, hop_count) and (last_seen DESC); there is no (iata, last_seen DESC).

Suggested fix.

• Add CREATE INDEX ... ON known_routes (iata, last_seen DESC).
• Replace the OR-optional filters with real optional parameters (sqlc.narg) or separate query variants so iata = $1 is visible to the planner when set. EXPLAIN ANALYZE with iata='GPT' before/after to confirm.
• Independently: the HTTP server has no request timeout (cmd/beacon/main.go http.Server sets none), so a slow query holds a worker for as long as the proxy waits. A ReadHeaderTimeout plus a per-request context.WithTimeout in the handlers (or http.TimeoutHandler) would bound this.

[n30nex]

Starting a focused follow-up on the merged dev baseline. I will reproduce the sparse-IATA case in a temporary PostgreSQL fixture, compare custom and generic prepared plans, and check whether an index alone is sufficient before changing query variants. The PR will preserve the current route/filter/cursor response behavior and stay separate from HTTP timeouts or other API changes. Native Pi measurements and the exact preview build will accompany it.