Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 2AE409FB5CC for ; Thu, 19 Jul 2007 21:41:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 06160-09 for ; Thu, 19 Jul 2007 21:41:26 -0300 (ADT) X-Greylist: delayed 12:49:17.051028 by SQLgrey-1.7.5 Received: from servidor.linux27.com (servidor.linux27.com [72.232.233.130]) by postgresql.org (Postfix) with ESMTP id 74C6A9FB5AE for ; Thu, 19 Jul 2007 21:41:31 -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 1IBgYd-0003sd-1B for pgsql-performance@postgresql.org; Thu, 19 Jul 2007 21:41:28 -0300 Reply-To: From: "Carlos H. Reimer" To: Subject: Improving select peformance Date: Thu, 19 Jul 2007 21:41:27 -0300 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0018_01C7CA4D.8FB70FA0" 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.316 tagged_above=0 required=5 tests=AWL=0.314, BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200707/251 X-Sequence-Number: 25935 This is a multi-part message in MIME format. ------=_NextPart_000_0018_01C7CA4D.8FB70FA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit 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_0018_01C7CA4D.8FB70FA0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
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_0018_01C7CA4D.8FB70FA0--