Thread: Improve response time of a SQL command
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'
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)
-----------------------------------------------------------
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
"Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
> I would like to improve the response time of the following SQL command
Try getting rid of the silly concatenate-with-empty-string operations
that appear to lurk within some view underlying this command. Those
seem to be defeating the planner's ability to guess how many join rows
there will be.
> -> Nested Loop (cost=4.69..2231.03
> rows=12 width=1264) (actual time=3.775..6353.475 rows=32069 loops=1)
> -> Hash Join (cost=4.69..2081.99
> rows=11 width=658) (actual time=3.257..1305.769 rows=32069 loops=1)
> Hash Cond:
> (("outer".codcor)::text = (("inner".codite)::text || ''::text))
It would surely not have used a nestloop here if it had had a better
idea of how many rows would come out of the lower join ... but since it
has no statistics about the result of the concatenate, it's just
guessing about that.
regards, tom lane