Re: Query - CPU issue

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Jayadevan M
Sent: Wednesday, September 18, 2013 9:08 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] 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