Jayadevan M <maymala.jayadevan@xxxxxxxxx> wrote: > explain analyze > select distinct geoip_city(src_ip) , src_ip > from alert where timestamp>=1378512000 and timestamp < 1378598400 > > The explain takes forever What is the longest you have let it run, in hours or minutes? > 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 > The function doesn't do much, code given below - But it is called 10,515 times -- even a few milliseconds per call can add up. > 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; Try running the SELECT from the function with different values in place of $1: common versus uncommon (or even missing) and low values versus high values. Show the EXPLAIN ANALYZE output of the longest-running. By the way, IMMUTABLE has to be wrong here, since the results depend on the state of the database. STABLE is likely the right designation. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
