Home > mailing lists

Improve response time of a SQL command - Mailing list pgsql-general

From	Carlos H. Reimer
Subject	Improve response time of a SQL command
Date	December 28, 2006 10:39:42
Msg-id	PEEPKDFEHHEMKBBFPOOKKENNDMAA.carlos.reimer@opendb.com.br Whole thread Raw
Responses	Re: Improve response time of a SQL command
List	pgsql-general

Tree view

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 by date:

From: "Alexander Farber"
Date: 28 December 2006, 08:51:21
Subject: Re: convert(... using windows_1251_to_utf8) - works on cli, but not in a c prog.

From: Akbar
Date: 28 December 2006, 11:01:25
Subject: unique constraint on more than one tables

Improve response time of a SQL command - Mailing list pgsql-general

Previous

Next