Improve response time of a SQL command

"Carlos H. Reimer" <carlos.reimer@xxxxxxxxxxxxx> · Thu, 28 Dec 2006 09:39:34 -0200

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