X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5BE41475D91 for ; Mon, 31 Mar 2003 16:13:18 -0500 (EST) Received: from spirit.aldeiadigital.com.br (iplus-fac-213-137.xdsl-fixo.ctbcnetsuper.com.br [200.225.213.137]) by postgresql.org (Postfix) with ESMTP id DA91D475CEE for ; Mon, 31 Mar 2003 16:13:15 -0500 (EST) Received: from aldeiadigital.com.br (localhost.localdomain [127.0.0.1]) by spirit.aldeiadigital.com.br (8.11.6/8.11.6) with SMTP id h2VLDQa01207 for ; Mon, 31 Mar 2003 18:13:27 -0300 Received: from 200.225.202.15 (SquirrelMail authenticated user alepaes) by webmail.ad2.com.br with HTTP; Mon, 31 Mar 2003 18:13:27 -0300 (BRT) Message-ID: <10846.200.225.202.15.1049145207.squirrel@webmail.ad2.com.br> Date: Mon, 31 Mar 2003 18:13:27 -0300 (BRT) Subject: 30-70 seconds query... From: "alexandre :: aldeia digital" To: X-Priority: 3 Importance: Normal X-MSMail-Priority: Normal X-Mailer: SquirrelMail (version 1.2.7) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Virus-Scanned: by amavisd-milter (http://amavis.org/) Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-3.0 required=5.0 tests=BAYES_20,MISSING_MIMEOLE,MISSING_OUTLOOK_NAME version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200303/204 X-Sequence-Number: 1502 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=B4s 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 =3D T1.es10codemp AND T2.es10datlan =3D T1.es10datlan AND T2.es10tipdoc =3D T1.es10tipdoc AND T2.fi08codigo =3D T1.fi08codigo AND T2.es10numdoc =3D T1.es10numdoc) LEFT JOIN FI08T T3 ON T3.fi08ufemp =3D T2.fi08ufemp AND T3.fi08codigo =3DT1.fi08codigo) LEFT JO= IN ES10T1 T4 ON T4.es10codemp =3D T1.es10codemp AND T4.es10datlan =3D T1.es10datlan AND T4.es10tipdoc =3D T1.es10tipdoc AND T4.fi08codigo =3D T1.fi08codigo AND T4.es10numdoc =3D T1.es10numdoc AND T4.es10item =3D T1.es10item) WHERE ( T4.co13Emp06 =3D '1' AND T4.co13CodPro =3D '16998' A= ND T1.es10datlan >=3D '2003-02-01'::date ) AND ( T1.es10datlan >=3D '2003-02-01'::date) AND ( T3.fi08MovEst =3D 'S' ) AND ( T4.es10empa =3D '1'= OR ( '1' =3D 0 ) ) AND ( T4.es10codalm =3D '0' OR T4.es10almtra =3D '0' OR (= '0' =3D 0 ) ) AND ( T1.es10datlan <=3D '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=3D379749.51..379833.81 rows=3D33722 width=3D142) (actual time=3D74031.72..74031.72 rows=3D0 loops=3D1) 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=3D1160.89..377213.38 rows=3D33722 width=3D142) (a= ctual time=3D74031.18..74031.18 rows=3D0 loops=3D1) Filter: (("inner".co13emp06 =3D 1::smallint) AND ("inner".co13codpro =3D 16998) AND ("inner".es10empa =3D 1::smallint)) -> Hash Join (cost=3D1160.89..173492.20 rows=3D33722 width=3D99) (actual time=3D35.98..27046.08 rows=3D33660 loops=3D1) Hash Cond: ("outer".fi08codigo =3D "inner".fi08codigo) Join Filter: ("inner".fi08ufemp =3D "outer".fi08ufemp) Filter: ("inner".fi08movest =3D 'S'::bpchar) -> Hash Join (cost=3D1120.19..172524.13 rows=3D33722 width=3D86) (actual time=3D33.64..26566.83 rows=3D33660 loops=3D1) Hash Cond: ("outer".es10datlan =3D "inner".es10datlan) Join Filter: (("inner".es10codemp =3D "outer".es10codemp) AND ("inner".es10tipdoc =3D "outer".es10tipdoc) AND ("inner".fi08codigo =3D "outer".fi08codigo) AND ("inner".es10numdoc =3D "outer".es10numdoc)) -> Index Scan using es10t2_ad1 on es10t2 t1=20 (cost=3D0.00..1148.09 rows=3D33722 width=3D51) (actual time=3D0.08..1885.06 rows=3D33660 loops=3D1) Index Cond: ((es10datlan >=3D '2003-02-01'::date) AND (es10datlan <=3D '2003-02-28'::date)) -> Hash (cost=3D1109.15..1109.15 rows=3D4415 width= =3D35) (actual time=3D33.23..33.23 rows=3D0 loops=3D1) -> Seq Scan on es10t t2 (cost=3D0.00..1109.15 rows=3D4415 width=3D35) (actual time=3D0.03..24.63 rows=3D4395 loops=3D1) -> Hash (cost=3D40.16..40.16 rows=3D216 width=3D13) (actual time=3D1.91..1.91 rows=3D0 loops=3D1) -> Seq Scan on fi08t t3 (cost=3D0.00..40.16 rows=3D2= 16 width=3D13) (actual time=3D0.03..1.46 rows=3D216 loops=3D1) -> Index Scan using es10t1_pkey on es10t1 t4 (cost=3D0.00..6.01 rows=3D1 width=3D43) (actual time=3D1.38..1.39 rows=3D1 loops=3D33660) Index Cond: ((t4.es10codemp =3D "outer".es10codemp) AND (t4.es10datlan =3D "outer".es10datlan) AND (t4.es10tipdoc =3D "outer".es10tipdoc) AND (t4.fi08codigo =3D "outer".fi08codigo) AND (t4.es10numdoc =3D "outer".es10numdoc) AND (t4.es10item =3D "outer".es10item)) Total runtime: 74032.60 msec (20 rows)