From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Jayadevan M Sent: Wednesday, September 18, 2013 9:08 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: Query - CPU issue 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 ----------------------------------------------------------------------------------------------------------------------- Did you try to do just EXPLAIN, not EXPLAIN ANALYZE, to see what's coming without actually executing the query? Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
