Re: Query - CPU issue

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