Jayadevan M <maymala.jayadevan@gmail.com> 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
