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

Поиск
Список
Период
Сортировка
От Andrus
Тема CPU usage goes to 100%, query seems to ran forever
Дата
Msg-id e2r3h0$1jto$1@news.hub.org
обсуждение исходный текст
Ответы Re: CPU usage goes to 100%, query seems to ran forever
Список 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.



В списке pgsql-performance по дате отправления:

Предыдущее
От: Andreas Kretschmer
Дата:
Сообщение: Re: Why so slow?
Следующее
От: Dan Gorman
Дата:
Сообщение: Re: Running on an NFS Mounted Directory