From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Query - CPU issue |
Date: | 2013-09-18 15:20:06 |
Message-ID: | 1379517606.54276.YahooMailNeo@web162904.mail.bf1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Kane | 2013-09-18 15:24:16 | Re: Query plan for currently executing query? |
Previous Message | Suzuki Hironobu | 2013-09-18 14:43:51 | Re: 9.2 Replication in Ubuntu ; need help |