Problems with posting

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