- Ya
- 10 Agosti 2026, 18:14 UTC
- Mwandishi
- Kamo
- Ahadi ya
- 99adac9
The IP lookup is a range scan: WHERE network_start <= ? ORDER BY network_start DESC LIMIT 1 YugabyteDB partitions the LEADING index column by HASH unless told otherwise; CockroachDB indexes are always range-ordered. So when the schema was rebuilt on YugabyteDB, idx_geolite_blocks_range became (network_start HASH, network_end ASC) and stopped being able to serve that predicate at all. Measured: full scan of all 5,820,022 rows, 8,814 ms per call. It was the single most expensive thing in the database -- 3.96 HOURS of cumulative execution time across 455 calls, plus a second geo join at 16.4 s average. It is also what exhausted SecurityService's connection pool. With (network_start ASC, network_end ASC): 6.9 ms and 0 rows scanned. The join query goes 16,421 ms -> 10.2 ms. Fixed in two places, because the table is rebuilt weekly: - GeoLiteBlock @Index, so fresh schema builds are correct. - DaemonService GeoLiteSyncService, whose staging+rename swap recreates the indexes every Sunday and would otherwise silently undo this.