[GENERAL] Query - CPU issue

[Jayadevan M]

Hi,
I have this query

explain analyze
select  distinct geoip_city(src_ip) , src_ip
from alert where timestamp>=1378512000 and timestamp < 1378598400

The explain takes forever, and CPU goes upto 100%. So I end up killing the
query/explain.

This one, without the function call, comes back in under a second -

explain analyze
select  distinct
 src_ip
from alert where timestamp>=1378512000 and timestamp < 1378598400
"HashAggregate  (cost=493.94..494.40 rows=46 width=8) (actual
time=38.669..38.684 rows=11 loops=1)"
"  ->  Index Scan using idx_alert_ts on alert  (cost=0.29..468.53
rows=10162 width=8) (actual time=0.033..20.436 rows=10515 loops=1)"
"        Index Cond: (("timestamp" >= 1378512000) AND ("timestamp" <
1378598400))"
"Total runtime: 38.740 ms"

The function doesn't do much, code given below -
CREATE OR REPLACE FUNCTION geoip_city(IN p_ip bigint, OUT loc_desc
character varying)
  RETURNS character varying AS
$BODY$
SELECT l.id || l.country ||l.region || l.city  FROM blocks b JOIN locations
l ON (b.location_id = l.id)
     WHERE $1 >= start_ip and $1 <= end_ip limit 1 ;
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;

There are indexes on the start_ip and end_ip and an explain tells me the
indexes are being used (if I execute the SELECT in the function using a
valid value for the ip value.

Regards,
Jayadevan