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

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: CPU usage goes to 100%, query seems to ran forever
Date: 2006-04-27 18:44:49
Message-ID: e2r3h0$1jto$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-performance
I have small database running in  8.1.3 in W2K server.
The following query causes Postgres process to use 100% CPU and seems to run 
forever.
If I change '1EEKPANT' to less frequently used item code, it runs fast.

How to speed it up ?

set search_path to public,firma2;
 select      rid.toode
   FROM dok JOIN rid USING (dokumnr)
   JOIN toode USING (toode)
   LEFT JOIN artliik ON toode.grupp=artliik.grupp and
    toode.liik=artliik.liik
   WHERE (NOT '0'  or dok.kinnitatud)
  AND dok.kuupaev BETWEEN '2006-04-08' AND '2006-04-27'
  AND rid.toode='1EEKPANT'
 AND (NOT dok.eimuuda or '0' ) and
 dok.laonr='1'::float8  and
 POSITION( dok.doktyyp IN 'OSIDVGYKIF')!=0 AND
 ( ( ('1'  OR (POSITION(dok.doktyyp IN 'TUNH')=0 and
       (rid.kogus<0 or
    ('1'  and rid.kogus=0))))
     and
         POSITION(dok.doktyyp IN 'VGYKITDNHMEBARCFJ' )!=0
          AND CASE WHEN NOT dok.objrealt OR dok.doktyyp='I' THEN dok.yksus 
ELSE rid.kuluobjekt END LIKE 'LADU%' ESCAPE '!'
      )
     OR
     (POSITION(dok.doktyyp IN 'OSIUDP' )!=0
          AND CASE WHEN dok.objrealt THEN rid.kuluobjekt ELSE dok.sihtyksus 
END LIKE 'LADU%' ESCAPE '!'
      )
   )
  AND dok.kuupaev||dok.kellaaeg BETWEEN '2006-04-08' AND '2006-04-2723 59'
 AND ('0'  or ( length(trim(rid.toode))>2 AND
     rid.toode is NOT NULL))

 AND ( LENGTH('' )=0 OR rid.partii=''  OR (dok.doktyyp='I' AND
        rid.kulupartii=''  ) )
 AND (NOT dok.inventuur or rid.kogus!=0)
   AND dok.dokumnr!= 0
 AND ( artliik.arttyyp NOT IN ('Teenus', 'Komplekt' ) OR artliik.arttyyp IS 
NULL)


explain returns:

"Nested Loop Left Join  (cost=0.00..1828.18 rows=1 width=24)"
"  Filter: ((("inner".arttyyp <> 'Teenus'::bpchar) AND ("inner".arttyyp <> 
'Komplekt'::bpchar)) OR ("inner".arttyyp IS NULL))"
"  ->  Nested Loop  (cost=0.00..1822.51 rows=1 width=43)"
"        ->  Nested Loop  (cost=0.00..1816.56 rows=1 width=24)"
"              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)"
"                    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=249 width=51)"
"                    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.94 rows=1 
width=43)"
"              Index Cond: ('1EEKPANT'::bpchar = toode)"
"  ->  Index Scan using artliik_pkey on artliik  (cost=0.00..5.65 rows=1 
width=88)"
"        Index Cond: (("outer".grupp = artliik.grupp) AND ("outer".liik = 
artliik.liik))"


Andrus. 



Responses

pgsql-performance by date

Next:From: Dan GormanDate: 2006-04-27 18:58:59
Subject: Re: Running on an NFS Mounted Directory
Previous:From: Andreas KretschmerDate: 2006-04-27 18:28:23
Subject: Re: Why so slow?

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