Problems with posting

Поиск
Список
Период
Сортировка
От Carlos H. Reimer
Тема Problems with posting
Дата
Msg-id PEEPKDFEHHEMKBBFPOOKEEMAFBAA.carlos.reimer@opendb.com.br
обсуждение исходный текст
Список pgsql-performance
Дерево обсуждения
Hi, I'm trying to post the following message to the performance group but the message does not appears in the list.
 
Can someone help to solve this issue?
 
Thanks in advance!
 
_______________________________________________________________________________________________________________________________
 
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 по дате отправления:

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