Skip site navigation (1) Skip section navigation (2)

Re: CPU usage goes to 100%, query seems to ran forever

From: "Andrus" <eetasoft(at)online(dot)ee>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: CPU usage goes to 100%, query seems to ran forever
Date: 2006-04-28 09:00:35
Message-ID: e2sljl$2hdf$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-performance
> You have ANALYZEd all these tables recently, I hope?  The planner
> certainly doesn't think this query will take very long.

I have autovacuum running so I expect it takes care of ANALYZE, isn't it ?

I ran also analyze command before running explain analyze.

> To find out what's wrong, you're going to have to be patient enough to
> let an EXPLAIN ANALYZE run to completion.  Plain EXPLAIN won't tell.

Here it is running in my local computer. I'm expecting run time no more 1 
second

"Nested Loop Left Join  (cost=0.00..1829.95 rows=1 width=24) (actual
time=492064.990..492064.990 rows=0 loops=1)"
"  Filter: ((("inner".arttyyp <> 'Teenus'::bpchar) AND ("inner".arttyyp <>
'Komplekt'::bpchar)) OR ("inner".arttyyp IS NULL))"
"  ->  Nested Loop  (cost=0.00..1825.01 rows=1 width=43) (actual
time=492064.983..492064.983 rows=0 loops=1)"
"        ->  Nested Loop  (cost=0.00..1819.04 rows=1 width=24) (actual
time=492064.978..492064.978 rows=0 loops=1)"
"              Join Filter: (("outer".dokumnr = "inner".dokumnr) AND
((("position"('VGYKITDNHMEBARCFJ'::text, ("outer".doktyyp)::text) <> 0) AND
(CASE WHEN ((NOT ("outer".objrealt)::boolean) OR ("outer".doktyyp =
'I'::bpchar)) THEN "outer".yksus ELSE "inner (..)"
"              ->  Seq Scan on dok  (cost=0.00..787.80 rows=1 width=39)
(actual time=0.152..878.198 rows=7670 loops=1)"
"                    Filter: ((kuupaev >= '2006-04-08'::date) AND (kuupaev
<= '2006-04-27'::date) AND (NOT (eimuuda)::boolean) AND ((laonr)::double
precision = 1::double precision) AND ("position"('OSIDVGYKIF'::text,
(doktyyp)::text) <> 0) AND (((kuupaev):: (..)"
"              ->  Seq Scan on rid  (cost=0.00..1019.42 rows=315 width=51)
(actual time=22.003..62.216 rows=839 loops=7670)"
"                    Filter: ((toode = '1EEKPANT'::bpchar) AND
(length(btrim((toode)::text)) > 2) AND (toode IS NOT NULL))"
"        ->  Index Scan using toode_pkey on toode  (cost=0.00..5.96 rows=1
width=43) (never executed)"
"              Index Cond: ('1EEKPANT'::bpchar = toode)"
"  ->  Index Scan using artliik_pkey on artliik  (cost=0.00..4.92 rows=1
width=31) (never executed)"
"        Index Cond: (("outer".grupp = artliik.grupp) AND ("outer".liik =
artliik.liik))"
"Total runtime: 492065.840 ms"


Andrus. 



In response to

Responses

pgsql-performance by date

Next:From: gulsahDate: 2006-04-28 11:30:59
Subject: query performance question
Previous:From: Markus SchaberDate: 2006-04-28 08:59:10
Subject: Re: how unsafe (or worst scenarios) when setting fsync

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group