Re: Query - CPU issue

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.