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
