Thank you for the pointers. I will try those suggestions. As I mentioned
later, resolving the query solved the problem for now.
Regards,
Jayadevan
On Thu, Sep 19, 2013 at 11:40 AM, Kevin Grittner-5 [via PostgreSQL] <
ml-node+s1045698n5771567...@n5.nabble.com> wrote:
> Jayadevan <[hidden
> email
Jayadevan wrote:
> "Merge Join (cost=9268.34..26193.41 rows=6282 width=24) (actual
> time=892.188..892.190 rows=1 loops=1)"
> " Merge Cond: (l.id = b.location_id)"
> " -> Index Scan using locations_pkey on locations l (cost=0.42..15739.22
> rows=438386 width=24) (actual time=0.022..865.025
Rewriting the query in the function like this solved the issue (this is how
it was in the SQL at
https://github.com/tvondra/geoip/blob/master/sql/geoip--0.1.0.sql)
explain analyze
SELECT l.id, l.country, l.region, l.city FROM blocks b JOIN locations l ON
(b.location_id = l.id)
WHERE 37211969
Kevin Grittner-5 wrote
> We already had this. I was asking for you to get EXPLAIN ANALYZE
> output for a run of the SELECT statement inside the geoip_city()
> function.
"Merge Join (cost=9268.34..26193.41 rows=6282 width=24) (actual
time=892.188..892.190 rows=1 loops=1)"
" Merge Cond: (l.id = b
Jayadevan wrote:
> Kevin Grittner-5 wrote
>> What is the longest you have let it run, in hours or minutes?
>
> I let it run for about 10 minutes and killed it.
Doing the arithmetic, that means if the estimated row counts from
the explain of the outer query are right, the function must average
at
Kevin Grittner-5 wrote
> What is the longest you have let it run, in hours or minutes?
I let it run for about 10 minutes and killed it.
Kevin Grittner-5 wrote
> By the way, IMMUTABLE has to be wrong here, since the results
> depend on the state of the database. STABLE is likely the right
> desig
On Wed, Sep 18, 2013 at 6:08 AM, Jayadevan M wrote:
>
> 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
Jayadevan M 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 i
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@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
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
10 matches
Mail list logo
