Improve response time of a SQL command

Поиск
Список
Период
Сортировка
От Carlos H. Reimer
Тема Improve response time of a SQL command
Дата
Msg-id PEEPKDFEHHEMKBBFPOOKKENNDMAA.carlos.reimer@opendb.com.br
обсуждение исходный текст
Ответы Re: Improve response time of a SQL command  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Дерево обсуждения
 
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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Alexander Farber"
Дата:
Сообщение: Re: convert(... using windows_1251_to_utf8) - works on cli, but not in a c prog.
Следующее
От: Akbar
Дата:
Сообщение: unique constraint on more than one tables