Geolocation EXPLAINed
summary
Builds a geolocation lookup using MaxMind data in two tables (network_blocks → network_locations), Rails, and Postgres, parsing CIDR to range-search IPs. Initial query does a sequential scan over ~1M of 3M rows and costs ~200 ms. Using Postgres EXPLAIN ANALYZE reveals a nested-loop plus seq scan; adding an index on start/end IPs and an ORDER to force an index scan drops runtime to ~1.67 ms — over 100× faster — so the system works and scales.
AI-written summary · about
chapters (16)
- 0:00Geolocation challenges
- 2:50Geolocation problem and goals
- 3:10Initial geolocation options
- 4:11MaxMind geolocation data
- 5:12CIDR notation for IP ranges
- 7:44Geolocation lookup in Rails
- 9:30Performance bottlenecks with New Relic
- 10:34EXPLAIN ANALYZE for slow queries
- 12:42Sequential vs index scan
- 16:14Nested loop joins impact
- 18:03B-tree indexes for search speed
- 21:57Indexing trade-offs with writes
- 23:22Optimizing geolocation query
- 24:44PostgreSQL query plan tuning
- 26:10Query optimization gains
- 26:55Summary
related talks
53mOn Beyond Data Types
43mHow to make an accurate Geo-IP database
46mGetting Down To Earth: Geospatial Analysis With Rails
44mPostgreSQL for Developers
18mFast IP Address Matching in Elixir (...) & Persistent Term
53mBreakout Session
32mEXPLAIN: make it make sense
23mLocation Insights for Your Graphs with Neo4j, PostGIS, and Brickgraph