Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id BB29D9FB3B2; Thu, 19 Jul 2007 22:19:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 49302-08; Thu, 19 Jul 2007 22:19:29 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from servidor.linux27.com (servidor.linux27.com [72.232.233.130]) by postgresql.org (Postfix) with ESMTP id D0DFA9FB3AF; Thu, 19 Jul 2007 22:19:34 -0300 (ADT) Received: from 201-67-132-139.bnut3700.dsl.brasiltelecom.net.br ([201.67.132.139] helo=PC1) by servidor.linux27.com with esmtpa (Exim 4.66) (envelope-from ) id 1IBh9T-0008PL-CV; Thu, 19 Jul 2007 22:19:33 -0300 Reply-To: From: "Carlos H. Reimer" To: Cc: Subject: Problems with posting Date: Thu, 19 Jul 2007 22:19:35 -0300 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001E_01C7CA52.E32E2650" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138 Importance: Normal X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - servidor.linux27.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - opendb.com.br X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.29 tagged_above=0 required=5 tests=AWL=0.288, BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200707/253 X-Sequence-Number: 25937 This is a multi-part message in MIME format. ------=_NextPart_000_001E_01C7CA52.E32E2650 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit Hi, I'm trying to post the following message to the performance group but the message does not appears in the list. Can someone help to solve this issue? Thanks in advance! ____________________________________________________________________________ ___________________________________________________ Hi, One of our end users was complaining about a report that was taking too much time to execute and I´ve discovered that the following SQL statement was the responsible for it. I would appreciate any suggestions to improve performance of it. Thank you very much in advance! ____________________________________________________________________________ _________________________________________________ explain analyze select (VEN.DOCUME)::varchar(13) as COLUNA0, (VENCODPGT.APEPGT)::varchar(9) as COLUNA1, (COALESCE(COALESCE(VEN.VLRLIQ,0) * (CASE VEN.VLRNOT WHEN 0 THEN 0 ELSE IVE.VLRMOV / VEN.VLRNOT END),0)) as COLUNA2, (COALESCE(IVE.QTDMOV,0)) as COLUNA3, (VIPR.NOMPRO)::varchar(83) as COLUNA4, (VIPR.REFPRO)::varchar(20) as COLUNA5 from TV_VEN VEN inner join TT_IVE IVE ON IVE.SEQUEN = VEN.SEQUEN and IVE.CODFIL = VEN.CODFIL inner join TV_IPR VIPR ON VIPR.FILMAT = IVE.FILMAT and VIPR.CODMAT = IVE.CODMAT and VIPR.CODCOR = IVE.CODCOR and VIPR.CODTAM = IVE.CODTAM left join TT_PLA VENCODPGT ON VEN.FILPGT = VENCODPGT.FILPGT AND VEN.CODPGT = VENCODPGT.CODPGT where ('001' = VEN.CODFIL) and VEN.DATHOR between '07/12/2007 00:00:00' and '07/12/2007 23:59:59' and (VEN.CODNAT = '-3') and IVE.SITMOV <> 'C' and ('1' = VIPR.DEPART) ; ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------------- Nested Loop Left Join (cost=995.52..75661.01 rows=1 width=195) (actual time=4488.166..1747121.374 rows=256 loops=1) -> Nested Loop (cost=995.52..75660.62 rows=1 width=199) (actual time=4481.323..1747105.903 rows=256 loops=1) Join Filter: ((gra.filmat = ive.filmat) AND (gra.codmat = ive.codmat) AND (gra.codcor = ive.codcor) AND (gra.codtam = ive.codtam)) -> Nested Loop (cost=1.11..3906.12 rows=1 width=151) (actual time=15.626..128.934 rows=414 loops=1) Join Filter: (div.coddiv = ddiv.codtab) -> Nested Loop (cost=1.11..3905.05 rows=1 width=160) (actual time=15.611..121.455 rows=414 loops=1) Join Filter: (sub.codsub = dsub.codtab) -> Nested Loop (cost=1.11..3903.99 rows=1 width=169) (actual time=15.593..113.866 rows=414 loops=1) Join Filter: ((gra.codcor)::text = ((div.codite)::text || ''::text)) -> Hash Join (cost=1.11..3888.04 rows=11 width=146) (actual time=15.560..85.376 rows=414 loops=1) Hash Cond: ((gra.codtam)::text = ((sub.codite)::text || ''::text)) -> Nested Loop (cost=0.00..3883.64 rows=423 width=123) (actual time=15.376..81.482 rows=414 loops=1) -> Index Scan using i_fk_pro_ddep on tt_pro pro (cost=0.00..149.65 rows=516 width=77) (actual time=15.244..30.586 rows=414 loops=1) Index Cond: (1::numeric = depart) -> Index Scan using pk_gra on tt_gra gra (cost=0.00..7.22 rows=1 width=46) (actual time=0.104..0.110 rows=1 loops=414) Index Cond: ((pro.filmat = gra.filmat) AND (pro.codmat = gra.codmat)) -> Hash (cost=1.05..1.05 rows=5 width=32) (actual time=0.048..0.048 rows=5 loops=1) -> Seq Scan on tt_sub sub (cost=0.00..1.05 rows=5 width=32) (actual time=0.016..0.024 rows=5 loops=1) -> Seq Scan on tt_div div (cost=0.00..1.15 rows=15 width=32) (actual time=0.004..0.022 rows=15 loops=414) -> Seq Scan on td_sub dsub (cost=0.00..1.03 rows=3 width=9) (actual time=0.003..0.007 rows=3 loops=414) -> Seq Scan on td_div ddiv (cost=0.00..1.03 rows=3 width=9) (actual time=0.002..0.007 rows=3 loops=414) -> Hash Join (cost=994.41..71746.74 rows=388 width=114) (actual time=5.298..4218.486 rows=857 loops=414) Hash Cond: (ive.sequen = ven.sequen) -> Nested Loop (cost=0.00..68318.52 rows=647982 width=85) (actual time=0.026..3406.170 rows=643739 loops=414) -> Seq Scan on td_nat nat (cost=0.00..1.24 rows=1 width=9) (actual time=0.004..0.014 rows=1 loops=414) Filter: (-3::numeric = codtab) -> Seq Scan on tt_ive ive (cost=0.00..61837.46 rows=647982 width=76) (actual time=0.017..1926.983 rows=643739 loops=414) Filter: ((sitmov <> 'C'::bpchar) AND ('001'::bpchar = codfil)) -> Hash (cost=992.08..992.08 rows=186 width=89) (actual time=33.234..33.234 rows=394 loops=1) -> Hash Left Join (cost=3.48..992.08 rows=186 width=89) (actual time=13.163..32.343 rows=394 loops=1) Hash Cond: ((ven.filcli = cfg.vc_filcli) AND (ven.codcli = cfg.vc_codcli)) -> Hash Join (cost=2.45..989.65 rows=186 width=106) (actual time=13.131..31.060 rows=394 loops=1) Hash Cond: ((ven.filpgt = pla.filpgt) AND (ven.codpgt = pla.codpgt)) -> Index Scan using i_lc_ven_dathor on tt_ven ven (cost=0.00..983.95 rows=186 width=106) (actual time=13.026..29.634 rows=394 loops=1) Index Cond: ((dathor >= '2007-07-12 00:00:00'::timestamp without time zone) AND (dathor <= '2007-07-12 23:59:59'::timestamp without time zone)) Filter: (('001'::bpchar = codfil) AND (codnat = -3::numeric)) -> Hash (cost=2.18..2.18 rows=18 width=14) (actual time=0.081..0.081 rows=18 loops=1) -> Seq Scan on tt_pla pla (cost=0.00..2.18 rows=18 width=14) (actual time=0.013..0.043 rows=18 loops=1) -> Hash (cost=1.01..1.01 rows=1 width=17) (actual time=0.017..0.017 rows=1 loops=1) -> Seq Scan on tt_cfg cfg (cost=0.00..1.01 rows=1 width=17) (actual time=0.010..0.011 rows=1 loops=1) -> Index Scan using pk_pla on tt_pla vencodpgt (cost=0.00..0.31 rows=1 width=24) (actual time=0.037..0.040 rows=1 loops=256) Index Cond: ((ven.filpgt = vencodpgt.filpgt) AND (ven.codpgt = vencodpgt.codpgt)) Total runtime: 1747122.219 ms (43 rows) ____________________________________________________________________________ _________________________________________________________ Table and view definitions can be accessed at: http://www.opendb.com.br/v1/problem0707.txt Reimer ------=_NextPart_000_001E_01C7CA52.E32E2650 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi, I'm trying to post the following message = to the=20 performance group but the message does not appears in the list.=20
 
Can someone help to solve this=20 issue?
 
Thanks in advance!
 
______________________________________________= _________________________________________________________________________= ________
 
Hi,
 
One = of our end=20 users was complaining about a report that was taking too much time to = execute=20 and I=B4ve discovered that the following SQL statement was the = responsible for it.=20
 
I = would appreciate=20 any suggestions to improve performance of it.
 
Thank = you very much=20 in advance!
 
______________________________________________= _________________________________________________________________________= ______
 
explain analyze select = (VEN.DOCUME)::varchar(13) as=20 COLUNA0, =20
           &nb= sp;  =20 (VENCODPGT.APEPGT)::varchar(9) as COLUNA1, =20
           &nb= sp;  =20 (COALESCE(COALESCE(VEN.VLRLIQ,0) * (CASE  VEN.VLRNOT  WHEN 0=20 THEN  0 ELSE  IVE.VLRMOV / VEN.VLRNOT  END),0)) as = COLUNA2, =20
           &nb= sp;  =20 (COALESCE(IVE.QTDMOV,0)) as COLUNA3, =20
           &nb= sp;  =20 (VIPR.NOMPRO)::varchar(83) as COLUNA4, =20
           &nb= sp;  =20 (VIPR.REFPRO)::varchar(20) as COLUNA5=20
        from TV_VEN VEN=20
           &nb= sp; =20 inner join TT_IVE IVE ON IVE.SEQUEN =3D VEN.SEQUEN and=20
        IVE.CODFIL =3D VEN.CODFIL =
           &nb= sp; =20 inner join TV_IPR VIPR ON VIPR.FILMAT =3D IVE.FILMAT and=20
        VIPR.CODMAT =3D = IVE.CODMAT and=20
        VIPR.CODCOR =3D = IVE.CODCOR and=20
        VIPR.CODTAM =3D = IVE.CODTAM=20
        =20
           &nb= sp;=20 left join TT_PLA VENCODPGT ON VEN.FILPGT =3D VENCODPGT.FILPGT AND = VEN.CODPGT =3D=20 VENCODPGT.CODPGT
        where = ('001' =3D=20 VEN.CODFIL)
        and = VEN.DATHOR=20 between '07/12/2007 00:00:00' and '07/12/2007 23:59:59'=20
        and (VEN.CODNAT =3D '-3') =
        and IVE.SITMOV <> = 'C'=20
        and ('1' =3D VIPR.DEPART) = ;
----------------------------------------------------------------= -------------------------------------------------------------------------= ----------------------------------------------
 Nested=20 Loop Left Join  (cost=3D995.52..75661.01 rows=3D1 width=3D195) = (actual=20 time=3D4488.166..1747121.374 rows=3D256 loops=3D1)
   = ->  Nested=20 Loop  (cost=3D995.52..75660.62 rows=3D1 width=3D199) (actual=20 time=3D4481.323..1747105.903 rows=3D256=20 loops=3D1)
         Join = Filter:=20 ((gra.filmat =3D ive.filmat) AND (gra.codmat =3D ive.codmat) AND = (gra.codcor =3D=20 ive.codcor) AND (gra.codtam =3D=20 ive.codtam))
         = -> =20 Nested Loop  (cost=3D1.11..3906.12 rows=3D1 width=3D151) (actual=20 time=3D15.626..128.934 rows=3D414=20 loops=3D1)
          = ;    =20 Join Filter: (div.coddiv =3D=20 ddiv.codtab)
         &nb= sp;    =20 ->  Nested Loop  (cost=3D1.11..3905.05 rows=3D1 = width=3D160) (actual=20 time=3D15.611..121.455 rows=3D414=20 loops=3D1)
          = ;          =20 Join Filter: (sub.codsub =3D=20 dsub.codtab)
         &nb= sp;          =20 ->  Nested Loop  (cost=3D1.11..3903.99 rows=3D1 = width=3D169) (actual=20 time=3D15.593..113.866 rows=3D414=20 loops=3D1)
          = ;            =     =20 Join Filter: ((gra.codcor)::text =3D ((div.codite)::text ||=20 ''::text))
          = ;            =     =20 ->  Hash Join  (cost=3D1.11..3888.04 rows=3D11 width=3D146) = (actual=20 time=3D15.560..85.376 rows=3D414=20 loops=3D1)
          = ;            =           =20 Hash Cond: ((gra.codtam)::text =3D ((sub.codite)::text ||=20 ''::text))
          = ;            =           =20 ->  Nested Loop  (cost=3D0.00..3883.64 rows=3D423 = width=3D123) (actual=20 time=3D15.376..81.482 rows=3D414=20 loops=3D1)
          = ;            =             &= nbsp;   =20 ->  Index Scan using i_fk_pro_ddep on tt_pro pro =20 (cost=3D0.00..149.65 rows=3D516 width=3D77) (actual = time=3D15.244..30.586 rows=3D414=20 loops=3D1)
          = ;            =             &= nbsp;         =20 Index Cond: (1::numeric =3D=20 depart)
          &n= bsp;           &nb= sp;           &nbs= p;   =20 ->  Index Scan using pk_gra on tt_gra gra  = (cost=3D0.00..7.22 rows=3D1=20 width=3D46) (actual time=3D0.104..0.110 rows=3D1=20 loops=3D414)
         &nb= sp;           &nbs= p;            = ;          =20 Index Cond: ((pro.filmat =3D gra.filmat) AND (pro.codmat =3D=20 gra.codmat))
         &nb= sp;           &nbs= p;          =20 ->  Hash  (cost=3D1.05..1.05 rows=3D5 width=3D32) (actual=20 time=3D0.048..0.048 rows=3D5=20 loops=3D1)
          = ;            =             &= nbsp;   =20 ->  Seq Scan on tt_sub sub  (cost=3D0.00..1.05 rows=3D5 = width=3D32)=20 (actual time=3D0.016..0.024 rows=3D5=20 loops=3D1)
          = ;            =     =20 ->  Seq Scan on tt_div div  (cost=3D0.00..1.15 rows=3D15 = width=3D32)=20 (actual time=3D0.004..0.022 rows=3D15=20 loops=3D414)
         &nb= sp;          =20 ->  Seq Scan on td_sub dsub  (cost=3D0.00..1.03 rows=3D3 = width=3D9)=20 (actual time=3D0.003..0.007 rows=3D3=20 loops=3D414)
         &nb= sp;    =20 ->  Seq Scan on td_div ddiv  (cost=3D0.00..1.03 rows=3D3 = width=3D9)=20 (actual time=3D0.002..0.007 rows=3D3=20 loops=3D414)
         = ->  Hash=20 Join  (cost=3D994.41..71746.74 rows=3D388 width=3D114) (actual=20 time=3D5.298..4218.486 rows=3D857=20 loops=3D414)
         &nb= sp;    =20 Hash Cond: (ive.sequen =3D=20 ven.sequen)
         &nbs= p;    =20 ->  Nested Loop  (cost=3D0.00..68318.52 rows=3D647982 = width=3D85) (actual=20 time=3D0.026..3406.170 rows=3D643739=20 loops=3D414)
         &nb= sp;          =20 ->  Seq Scan on td_nat nat  (cost=3D0.00..1.24 rows=3D1 = width=3D9)=20 (actual time=3D0.004..0.014 rows=3D1=20 loops=3D414)
         &nb= sp;           &nbs= p;    =20 Filter: (-3::numeric =3D=20 codtab)
          &n= bsp;         =20 ->  Seq Scan on tt_ive ive  (cost=3D0.00..61837.46 = rows=3D647982=20 width=3D76) (actual time=3D0.017..1926.983 rows=3D643739=20 loops=3D414)
         &nb= sp;           &nbs= p;    =20 Filter: ((sitmov <> 'C'::bpchar) AND ('001'::bpchar =3D=20 codfil))
          &= nbsp;   =20 ->  Hash  (cost=3D992.08..992.08 rows=3D186 width=3D89) = (actual=20 time=3D33.234..33.234 rows=3D394=20 loops=3D1)
          = ;          =20 ->  Hash Left Join  (cost=3D3.48..992.08 rows=3D186 = width=3D89) (actual=20 time=3D13.163..32.343 rows=3D394=20 loops=3D1)
          = ;            =     =20 Hash Cond: ((ven.filcli =3D cfg.vc_filcli) AND (ven.codcli =3D=20 cfg.vc_codcli))
         =             &= nbsp;    =20 ->  Hash Join  (cost=3D2.45..989.65 rows=3D186 width=3D106) = (actual=20 time=3D13.131..31.060 rows=3D394=20 loops=3D1)
          = ;            =           =20 Hash Cond: ((ven.filpgt =3D pla.filpgt) AND (ven.codpgt =3D=20 pla.codpgt))
         &nb= sp;           &nbs= p;          =20 ->  Index Scan using i_lc_ven_dathor on tt_ven ven =20 (cost=3D0.00..983.95 rows=3D186 width=3D106) (actual = time=3D13.026..29.634 rows=3D394=20 loops=3D1)
          = ;            =             &= nbsp;   =20 Index Cond: ((dathor >=3D '2007-07-12 00:00:00'::timestamp without = time zone)=20 AND (dathor <=3D '2007-07-12 23:59:59'::timestamp without time=20 zone))
          &nb= sp;           &nbs= p;            = ;   =20 Filter: (('001'::bpchar =3D codfil) AND (codnat =3D=20 -3::numeric))
         &n= bsp;           &nb= sp;          =20 ->  Hash  (cost=3D2.18..2.18 rows=3D18 width=3D14) (actual=20 time=3D0.081..0.081 rows=3D18=20 loops=3D1)
          = ;            =             &= nbsp;   =20 ->  Seq Scan on tt_pla pla  (cost=3D0.00..2.18 rows=3D18 = width=3D14)=20 (actual time=3D0.013..0.043 rows=3D18=20 loops=3D1)
          = ;            =     =20 ->  Hash  (cost=3D1.01..1.01 rows=3D1 width=3D17) (actual=20 time=3D0.017..0.017 rows=3D1=20 loops=3D1)
          = ;            =           =20 ->  Seq Scan on tt_cfg cfg  (cost=3D0.00..1.01 rows=3D1 = width=3D17)=20 (actual time=3D0.010..0.011 rows=3D1 loops=3D1)
   = ->  Index Scan=20 using pk_pla on tt_pla vencodpgt  (cost=3D0.00..0.31 rows=3D1 = width=3D24) (actual=20 time=3D0.037..0.040 rows=3D1=20 loops=3D256)
         Index = Cond:=20 ((ven.filpgt =3D vencodpgt.filpgt) AND (ven.codpgt =3D=20 vencodpgt.codpgt))
 Total runtime: 1747122.219 ms
(43=20 rows)
 
________________________________________________________________= _____________________________________________________________________
 
Table=20 and view definitions can be accessed at: http://www.opendb.co= m.br/v1/problem0707.txt
 
Reimer
<= /BODY> ------=_NextPart_000_001E_01C7CA52.E32E2650--