Hi,
I would like to improve the response time of the following SQL command but I
need some help to indentify where is it taking most of the processing time.
It seams that it is in the Seq Scan on tt_gra gra, but not sure. Am I right?
Can a new index help in this issue?
Thank you in advance!
explain analyze
SELECT IPR.REFPRO, IPR.NOMPRO, IPR.MEDUNI,
IVE.VLRMOV /IVE.QTDMOV AS PRECOV,
VEN.DESCON, IVE.QTDMOV,
COALESCE(IVE.TAX001,0) AS ICMS,
SUBSTR(SIT.DESDOM,1,30) AS SITUACAO,
IVE.VLRMOV AS TOTITE,
IOR.FILRES
FROM TT_IVE IVE LEFT OUTER JOIN TV_IPR IPR ON IPR.FILMAT =
IVE.FILMAT AND IPR.CODMAT = IVE.CODMAT AND IPR.CODCOR = IVE.CODCOR AND
IPR.CODTAM = IVE.CODTAM
LEFT OUTER JOIN TT_DOM SIT ON SIT.CODARQ = 'IVE'
AND SIT.NOMCPO = 'SITMOV' AND SIT.CODCHR = IVE.SITMOV
LEFT OUTER JOIN TT_VEN VEN ON IVE.CODFIL =
VEN.CODFIL AND IVE.SEQUEN = VEN.SEQUEN
LEFT OUTER JOIN TT_IOR IOR ON IVE.CODFIL =
IOR.FILIVE AND IVE.SEQUEN = IOR.SEQIVE AND IVE.NUMITE = IOR.NUMIVE
WHERE IVE.CODFIL= '001'
AND IVE.SEQUEN= ' 113519'
;
QUERY PLAN
----------------------------------------------------------------------------
-------------------------------------------------
-----------------------------------------------------------
Nested Loop Left Join (cost=2234.61..2326.73 rows=10 width=148) (actual
time=6641.168..6689.295 rows=1 loops=1)
-> Nested Loop Left Join (cost=2234.61..2291.17 rows=9 width=172)
(actual time=6630.985..6679.105 rows=1 loops=1)
-> Hash Left Join (cost=2234.61..2255.78 rows=9 width=163)
(actual time=6630.873..6678.987 rows=1 loops=1)
Hash Cond: ("outer".sitmov = "inner".codchr)
-> Nested Loop Left Join (cost=2231.16..2252.28 rows=9
width=154) (actual time=6628.171..6676.282 rows=1 loo
ps=1)
Join Filter: (("inner".filmat = "outer".filmat) AND
("inner".codmat = "outer".codmat) AND ("inner".codco
r = "outer".codcor) AND ("inner".codtam = "outer".codtam))
-> Index Scan using pk_ive on tt_ive ive
(cost=0.00..17.88 rows=9 width=98) (actual time=17.450..17.45
5 rows=1 loops=1)
Index Cond: ((codfil = '001'::bpchar) AND (sequen
= ' 113519'::bpchar))
-> Materialize (cost=2231.16..2231.28 rows=12
width=126) (actual time=3.803..6593.478 rows=32069 loops
=1)
-> Subquery Scan ipr (cost=4.69..2231.15
rows=12 width=126) (actual time=3.791..6541.255 rows=32
069 loops=1)
-> Nested Loop (cost=4.69..2231.03
rows=12 width=1264) (actual time=3.775..6353.475 rows=3
2069 loops=1)
-> Hash Join (cost=4.69..2081.99
rows=11 width=658) (actual time=3.257..1305.769 row
s=32069 loops=1)
Hash Cond:
(("outer".codcor)::text = (("inner".codite)::text || ''::text))
-> Hash Join
(cost=2.06..2077.23 rows=161 width=613) (actual time=1.386..697.0
22 rows=32069 loops=1)
Hash Cond:
(("outer".codtam)::text = (("inner".codite)::text || ''::text))
-> Seq Scan on tt_gra
gra (cost=0.00..1672.14 rows=32114 width=551) (act
ual time=0.047..78.800 rows=32069 loops=1)
-> Hash
(cost=2.06..2.06 rows=1 width=70) (actual time=0.087..0.087 rows
=0 loops=1)
-> Nested Loop
(cost=0.00..2.06 rows=1 width=70) (actual time=0.05
2..0.060 rows=1 loops=1)
Join Filter:
("outer".codsub = "inner".codtab)
-> Seq Scan
on tt_sub sub (cost=0.00..1.01 rows=1 width=48)
(actual time=0.014..0.015 rows=1 loops=1)
-> Seq Scan
on td_sub dsub (cost=0.00..1.02 rows=2 width=31)
(actual time=0.011..0.015 rows=2 loops=1)
-> Hash (cost=2.60..2.60
rows=13 width=54) (actual time=0.321..0.321 rows=0 lo
ops=1)
-> Merge Join
(cost=2.40..2.60 rows=13 width=54) (actual time=0.219..0.2
66 rows=13 loops=1)
Merge Cond:
("outer".codtab = "inner".coddiv)
-> Sort
(cost=1.03..1.03 rows=2 width=22) (actual time=0.120..0.12
2 rows=2 loops=1)
Sort Key:
ddiv.codtab
-> Seq Scan
on td_div ddiv (cost=0.00..1.02 rows=2 width=22)
(actual time=0.016..0.021 rows=2 loops=1)
-> Sort
(cost=1.37..1.40 rows=13 width=41) (actual time=0.075..0.0
83 rows=13 loops=1)
Sort Key:
div.coddiv
-> Seq Scan
on tt_div div (cost=0.00..1.13 rows=13 width=41)
(actual time=0.016..0.049 rows=13 loops=1)
-> Index Scan using ak_pro_tippro on
tt_pro pro (cost=0.00..3.60 rows=1 width=622) (
actual time=0.016..0.018 rows=1 loops=32069)
Index Cond: ((pro.filmat =
"outer".filmat) AND (pro.codmat = "outer".codmat))
SubPlan
-> Index Scan using ak_pre_gra on
tt_pre pre (cost=1.01..4.47 rows=1 width=11) (ac
tual time=0.028..0.030 rows=1 loops=32069)
Index Cond: ((filpre = $4)
AND (codpre = $5) AND (filmat = $6) AND (codmat = $
7) AND (codcor = $8) AND (codtam = $9))
InitPlan
-> Seq Scan on tt_cfg
(cost=0.00..1.01 rows=1 width=17) (actual time=0.022
..0.024 rows=1 loops=1)
-> Seq Scan on td_med med4
(cost=0.00..1.15 rows=1 width=6) (actual time=0.003..0.
012 rows=1 loops=32069)
Filter: ($3 = codtab)
-> Seq Scan on td_med med3
(cost=0.00..1.15 rows=1 width=6) (actual time=0.003..0.
012 rows=1 loops=32069)
Filter: ($2 = codtab)
-> Seq Scan on td_med med2
(cost=0.00..1.15 rows=1 width=6) (actual time=0.003..0.
012 rows=1 loops=32069)
Filter: ($1 = codtab)
-> Seq Scan on td_med med1
(cost=0.00..1.15 rows=1 width=6) (actual time=0.004..0.
015 rows=1 loops=32069)
Filter: ($0 = codtab)
-> Hash (cost=3.44..3.44 rows=1 width=19) (actual
time=0.106..0.106 rows=0 loops=1)
-> Index Scan using i_lc_dom_str on tt_dom sit
(cost=0.00..3.44 rows=1 width=19) (actual time=0.077..0
.084 rows=2 loops=1)
Index Cond: ((codarq = 'IVE'::bpchar) AND
((nomcpo)::text = 'SITMOV'::text))
-> Index Scan using pk_ven on tt_ven ven (cost=0.00..3.92 rows=1
width=30) (actual time=0.089..0.092 rows=1 loops=
1)
Index Cond: (("outer".codfil = ven.codfil) AND
("outer".sequen = ven.sequen))
-> Index Scan using i_uq_ior_ive on tt_ior ior (cost=0.00..3.93 rows=1
width=38) (actual time=8.524..8.528 rows=1 loops=
1)
Index Cond: (("outer".codfil = ior.filive) AND ("outer".sequen =
ior.seqive) AND ("outer".numite = ior.numive))
Total runtime: 6697.206 ms
(53 rows)
Reimer
