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.
