Improving select peformance

Поиск
Список
Период
Сортировка
От Carlos H. Reimer
Тема Improving select peformance
Дата
Msg-id PEEPKDFEHHEMKBBFPOOKMELPFBAA.carlos.reimer@opendb.com.br
обсуждение исходный текст
Ответы Re: Improving select peformance
Список pgsql-performance
Дерево обсуждения
Hi,
 
One of our end users was complaining about a report that was taking too much time to execute and I´ve discovered that the following SQL statement was the responsible for it.
 
I would appreciate any suggestions to improve performance of it.
 
Thank you very much in advance!
 
_____________________________________________________________________________________________________________________________
 
explain analyze select (VEN.DOCUME)::varchar(13) as COLUNA0, 
               (VENCODPGT.APEPGT)::varchar(9) as COLUNA1, 
               (COALESCE(COALESCE(VEN.VLRLIQ,0) * (CASE  VEN.VLRNOT  WHEN 0 THEN  0 ELSE  IVE.VLRMOV / VEN.VLRNOT  END),0)) as COLUNA2, 
               (COALESCE(IVE.QTDMOV,0)) as COLUNA3, 
               (VIPR.NOMPRO)::varchar(83) as COLUNA4, 
               (VIPR.REFPRO)::varchar(20) as COLUNA5
        from TV_VEN VEN
              inner join TT_IVE IVE ON IVE.SEQUEN = VEN.SEQUEN and
        IVE.CODFIL = VEN.CODFIL
              inner join TV_IPR VIPR ON VIPR.FILMAT = IVE.FILMAT and
        VIPR.CODMAT = IVE.CODMAT and
        VIPR.CODCOR = IVE.CODCOR and
        VIPR.CODTAM = IVE.CODTAM
        
             left join TT_PLA VENCODPGT ON VEN.FILPGT = VENCODPGT.FILPGT AND VEN.CODPGT = VENCODPGT.CODPGT
        where ('001' = VEN.CODFIL)
        and VEN.DATHOR between '07/12/2007 00:00:00' and '07/12/2007 23:59:59'
        and (VEN.CODNAT = '-3')
        and IVE.SITMOV <> 'C'
        and ('1' = VIPR.DEPART) ;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=995.52..75661.01 rows=1 width=195) (actual time=4488.166..1747121.374 rows=256 loops=1)
   ->  Nested Loop  (cost=995.52..75660.62 rows=1 width=199) (actual time=4481.323..1747105.903 rows=256 loops=1)
         Join Filter: ((gra.filmat = ive.filmat) AND (gra.codmat = ive.codmat) AND (gra.codcor = ive.codcor) AND (gra.codtam = ive.codtam))
         ->  Nested Loop  (cost=1.11..3906.12 rows=1 width=151) (actual time=15.626..128.934 rows=414 loops=1)
               Join Filter: (div.coddiv = ddiv.codtab)
               ->  Nested Loop  (cost=1.11..3905.05 rows=1 width=160) (actual time=15.611..121.455 rows=414 loops=1)
                     Join Filter: (sub.codsub = dsub.codtab)
                     ->  Nested Loop  (cost=1.11..3903.99 rows=1 width=169) (actual time=15.593..113.866 rows=414 loops=1)
                           Join Filter: ((gra.codcor)::text = ((div.codite)::text || ''::text))
                           ->  Hash Join  (cost=1.11..3888.04 rows=11 width=146) (actual time=15.560..85.376 rows=414 loops=1)
                                 Hash Cond: ((gra.codtam)::text = ((sub.codite)::text || ''::text))
                                 ->  Nested Loop  (cost=0.00..3883.64 rows=423 width=123) (actual time=15.376..81.482 rows=414 loops=1)
                                       ->  Index Scan using i_fk_pro_ddep on tt_pro pro  (cost=0.00..149.65 rows=516 width=77) (actual time=15.244..30.586 rows=414 loops=1)
                                             Index Cond: (1::numeric = depart)
                                       ->  Index Scan using pk_gra on tt_gra gra  (cost=0.00..7.22 rows=1 width=46) (actual time=0.104..0.110 rows=1 loops=414)
                                             Index Cond: ((pro.filmat = gra.filmat) AND (pro.codmat = gra.codmat))
                                 ->  Hash  (cost=1.05..1.05 rows=5 width=32) (actual time=0.048..0.048 rows=5 loops=1)
                                       ->  Seq Scan on tt_sub sub  (cost=0.00..1.05 rows=5 width=32) (actual time=0.016..0.024 rows=5 loops=1)
                           ->  Seq Scan on tt_div div  (cost=0.00..1.15 rows=15 width=32) (actual time=0.004..0.022 rows=15 loops=414)
                     ->  Seq Scan on td_sub dsub  (cost=0.00..1.03 rows=3 width=9) (actual time=0.003..0.007 rows=3 loops=414)
               ->  Seq Scan on td_div ddiv  (cost=0.00..1.03 rows=3 width=9) (actual time=0.002..0.007 rows=3 loops=414)
         ->  Hash Join  (cost=994.41..71746.74 rows=388 width=114) (actual time=5.298..4218.486 rows=857 loops=414)
               Hash Cond: (ive.sequen = ven.sequen)
               ->  Nested Loop  (cost=0.00..68318.52 rows=647982 width=85) (actual time=0.026..3406.170 rows=643739 loops=414)
                     ->  Seq Scan on td_nat nat  (cost=0.00..1.24 rows=1 width=9) (actual time=0.004..0.014 rows=1 loops=414)
                           Filter: (-3::numeric = codtab)
                     ->  Seq Scan on tt_ive ive  (cost=0.00..61837.46 rows=647982 width=76) (actual time=0.017..1926.983 rows=643739 loops=414)
                           Filter: ((sitmov <> 'C'::bpchar) AND ('001'::bpchar = codfil))
               ->  Hash  (cost=992.08..992.08 rows=186 width=89) (actual time=33.234..33.234 rows=394 loops=1)
                     ->  Hash Left Join  (cost=3.48..992.08 rows=186 width=89) (actual time=13.163..32.343 rows=394 loops=1)
                           Hash Cond: ((ven.filcli = cfg.vc_filcli) AND (ven.codcli = cfg.vc_codcli))
                           ->  Hash Join  (cost=2.45..989.65 rows=186 width=106) (actual time=13.131..31.060 rows=394 loops=1)
                                 Hash Cond: ((ven.filpgt = pla.filpgt) AND (ven.codpgt = pla.codpgt))
                                 ->  Index Scan using i_lc_ven_dathor on tt_ven ven  (cost=0.00..983.95 rows=186 width=106) (actual time=13.026..29.634 rows=394 loops=1)
                                       Index Cond: ((dathor >= '2007-07-12 00:00:00'::timestamp without time zone) AND (dathor <= '2007-07-12 23:59:59'::timestamp without time zone))
                                       Filter: (('001'::bpchar = codfil) AND (codnat = -3::numeric))
                                 ->  Hash  (cost=2.18..2.18 rows=18 width=14) (actual time=0.081..0.081 rows=18 loops=1)
                                       ->  Seq Scan on tt_pla pla  (cost=0.00..2.18 rows=18 width=14) (actual time=0.013..0.043 rows=18 loops=1)
                           ->  Hash  (cost=1.01..1.01 rows=1 width=17) (actual time=0.017..0.017 rows=1 loops=1)
                                 ->  Seq Scan on tt_cfg cfg  (cost=0.00..1.01 rows=1 width=17) (actual time=0.010..0.011 rows=1 loops=1)
   ->  Index Scan using pk_pla on tt_pla vencodpgt  (cost=0.00..0.31 rows=1 width=24) (actual time=0.037..0.040 rows=1 loops=256)
         Index Cond: ((ven.filpgt = vencodpgt.filpgt) AND (ven.codpgt = vencodpgt.codpgt))
 Total runtime: 1747122.219 ms
(43 rows)
 
_____________________________________________________________________________________________________________________________________
 
Table and view definitions can be accessed at: http://www.opendb.com.br/v1/problem0707.txt
 
Reimer

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

Предыдущее
От: Greg Smith
Дата:
Сообщение: Re: User concurrency thresholding: where do I look?
Следующее
От: Josh Berkus
Дата:
Сообщение: Re: User concurrency thresholding: where do I look?