Rewriting the query in the function like this solved the issue (this is how
it was in the SQL at
https://github.com/tvondra/geoip/blob/master/sql/geoip--0.1.0.sql)
explain analyze
SELECT l.id, l.country, l.region, l.city FROM blocks b JOIN locations l ON
(b.location_id = l.id)
WHERE 3721196957 >= start_ip ORDER BY start_ip DESC LIMIT 1
"Limit (cost=0.85..2.09 rows=1 width=32) (actual time=0.015..0.015 rows=1
loops=1)"
" -> Nested Loop (cost=0.85..2216242.97 rows=1784157 width=32) (actual
time=0.014..0.014 rows=1 loops=1)"
" -> Index Scan Backward using ix_start_end_ip on blocks b
(cost=0.43..934027.92 rows=1784157 width=16) (actual time=0.008..0.008
rows=1 loops=1)"
" Index Cond: (3721196957::bigint >= start_ip)"
" -> Index Scan using locations_pkey on locations l
(cost=0.42..0.71 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=1)"
" Index Cond: (id = b.location_id)"
"Total runtime: 0.039 ms"
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Query-CPU-issue-tp5771421p5771561.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
