30-70 seconds query... - Mailing list pgsql-performance

Hi all,

I use a case tool and we generate the querys automatically.
The query explained is a part of an Report and takes a long time
to complete (30 ~ 70 seconds). My machine is a Dual Xeon 2 Ghz, 1 Mb DDR,
3 SCSI HW RAID 5.
The tables involved in query have 500.000 rows.

Thank´s for any help...

Alexandre


explain analyze SELECT T2.fi08ufemp, T4.es10almtra, T3.fi08MovEst,
T1.es10qtdgra, T1.es10Tamanh,   T1.es10item, T1.es10numdoc, T1.fi08codigo,
T1.es10tipdoc, T1.es10codemp, T4.es10codalm,   T4.es10empa, T1.es10datlan,
T4.co13CodPro, T4.co13Emp06, T1.es10EmpTam  FROM (((ES10T2   T1 LEFT JOIN
ES10T T2 ON T2.es10codemp = T1.es10codemp AND T2.es10datlan =
T1.es10datlan   AND T2.es10tipdoc = T1.es10tipdoc AND T2.fi08codigo =
T1.fi08codigo AND T2.es10numdoc   = T1.es10numdoc) LEFT JOIN FI08T T3 ON
T3.fi08ufemp = T2.fi08ufemp AND T3.fi08codigo   =T1.fi08codigo) LEFT JOIN
ES10T1 T4 ON T4.es10codemp = T1.es10codemp AND T4.es10datlan   =
T1.es10datlan AND T4.es10tipdoc = T1.es10tipdoc AND T4.fi08codigo =
T1.fi08codigo   AND T4.es10numdoc = T1.es10numdoc AND T4.es10item =
T1.es10item) WHERE ( T4.co13Emp06   = '1' AND T4.co13CodPro = '16998' AND
T1.es10datlan >= '2003-02-01'::date ) AND ( T1.es10datlan >=
'2003-02-01'::date) AND ( T3.fi08MovEst = 'S' ) AND ( T4.es10empa = '1' OR
( '1' =   0 ) ) AND ( T4.es10codalm = '0' OR T4.es10almtra = '0' OR ( '0'
= 0 ) ) AND ( T1.es10datlan   <= '2003-02-28'::date ) ORDER BY
T4.co13Emp06, T4.co13CodPro, T1.es10datlan, T4.es10empa, T4.es10codalm,
T4.es10almtra,  T1.es10codemp, T1.es10tipdoc, T1.fi08codigo,
T1.es10numdoc, T1.es10item;


                              QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=379749.51..379833.81 rows=33722 width=142) (actual
time=74031.72..74031.72 rows=0 loops=1)
   Sort Key: t4.co13emp06, t4.co13codpro, t1.es10datlan, t4.es10empa,
t4.es10codalm, t4.es10almtra, t1.es10codemp, t1.es10tipdoc,
t1.fi08codigo, t1.es10numdoc, t1.es10item
   ->  Nested Loop  (cost=1160.89..377213.38 rows=33722 width=142) (actual
time=74031.18..74031.18 rows=0 loops=1)
         Filter: (("inner".co13emp06 = 1::smallint) AND
("inner".co13codpro = 16998) AND ("inner".es10empa =
1::smallint))
         ->  Hash Join  (cost=1160.89..173492.20 rows=33722 width=99)
(actual time=35.98..27046.08 rows=33660 loops=1)
               Hash Cond: ("outer".fi08codigo = "inner".fi08codigo)
               Join Filter: ("inner".fi08ufemp = "outer".fi08ufemp)
               Filter: ("inner".fi08movest = 'S'::bpchar)
               ->  Hash Join  (cost=1120.19..172524.13 rows=33722
width=86) (actual time=33.64..26566.83 rows=33660 loops=1)
                     Hash Cond: ("outer".es10datlan = "inner".es10datlan)
                     Join Filter: (("inner".es10codemp =
"outer".es10codemp) AND ("inner".es10tipdoc =
"outer".es10tipdoc) AND ("inner".fi08codigo =
"outer".fi08codigo) AND ("inner".es10numdoc =
"outer".es10numdoc))
                     ->  Index Scan using es10t2_ad1 on es10t2 t1
(cost=0.00..1148.09 rows=33722 width=51) (actual
time=0.08..1885.06 rows=33660 loops=1)
                           Index Cond: ((es10datlan >= '2003-02-01'::date)
AND (es10datlan <= '2003-02-28'::date))
                     ->  Hash  (cost=1109.15..1109.15 rows=4415 width=35)
(actual time=33.23..33.23 rows=0 loops=1)
                           ->  Seq Scan on es10t t2  (cost=0.00..1109.15
rows=4415 width=35) (actual time=0.03..24.63
rows=4395 loops=1)
               ->  Hash  (cost=40.16..40.16 rows=216 width=13) (actual
time=1.91..1.91 rows=0 loops=1)
                     ->  Seq Scan on fi08t t3  (cost=0.00..40.16 rows=216
width=13) (actual time=0.03..1.46 rows=216 loops=1)
         ->  Index Scan using es10t1_pkey on es10t1 t4  (cost=0.00..6.01
rows=1 width=43) (actual time=1.38..1.39 rows=1 loops=33660)
               Index Cond: ((t4.es10codemp = "outer".es10codemp) AND
(t4.es10datlan = "outer".es10datlan) AND (t4.es10tipdoc =
"outer".es10tipdoc) AND (t4.fi08codigo =
"outer".fi08codigo) AND (t4.es10numdoc =
"outer".es10numdoc) AND (t4.es10item = "outer".es10item))
 Total runtime: 74032.60 msec
(20 rows)