From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jayadevan M
Sent: Wednesday, September 18, 2013 9:08 AM
To: pgsql-general@postgresql.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=10515loops=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
SELECTin 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
