mirror of
https://github.com/ipnet-mesh/meshcore-hub.git
synced 2026-07-26 03:22:55 +02:00
Louis King
c9b247c553
The route evaluator's fetch_candidate_paths triggered a 10-30s full-table scan of packet_path_hops per route per sweep, flooding the slow-query log and blocking route saves on the same scan. EXPLAIN analysis (Merge Join vs forced Nested Loop, across query rewrites, ANALYZE, CTE MATERIALIZED) confirmed both candidate plans cost ~10-15s at 6.4M rows; no SQL rewrite or stats change could avoid the scan. The fix applies four orthogonal data-volume pressures: - ROUTE_EVALUATOR_INTERVAL_SECONDS default 60->300 (5x less frequent) - Remove synchronous _reevaluate_route on POST/PUT; route_result and recent_matches now refresh on the next background sweep instead of inline (kills save latency) - Route.window_hours default 48->6, max 12 (bounds the candidate set); migration clamps pre-existing routes >12 to 12 - Covering index ix_packet_path_hops_raw_packet_id_position INCLUDE (node_hash, packet_hash, event_hash, received_at, observer_node_id) so the Merge Join outer scan becomes index-only - RAW_PACKET_RETENTION_DAYS default 7->2 (hops cascade-delete with raw packets; smaller table cuts the dominant outer scan) Migration a59611449e2a clamps window_hours and rebuilds the index CONCURRENTLY on PostgreSQL (SQLite is a no-op; no INCLUDE support). DESTRUCTIVE on upgrade: the retention default change purges ~5 days of raw packets + cascaded hops on the next cleanup run. See docs/upgrading.md for the full change notes and the new save/sweep contract.