Hi List,

      I have posted a subjetc on the admin list but I thought that it might fit 
better on this list as follow:

Hi List,

     As I said before, I'm not a DBA " yet" , but I'm learning ... and I 
already have a PostgreSQL running, so I have to ask some help...
      I got a SQL as folows :
 
      SELECT /*+  */ 
ftnfco00.estado_cliente , 
ftcofi00.grupo_faturamento , 
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.qtde_duzias,0)),       '+', NVL(ftnfpr00.qtde_duzias,0),       0) ) , 
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL(ftnfpr00.vlr_liquido,0)), '+', 
NVL(ftnfpr00.vlr_liquido,0), 0) ) , 
ftprod00.tipo_cadastro||ftprod00.codigo_produto||'||'||gsames00.ano_mes , 
ftprod00.descricao_produto||'||'||gsames00.descricao , 
DIVIDE( SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.vlr_liquido,0)), '+', NVL(ftnfpr00.vlr_liquido,0), 0)
*ftnfpr00.margem_comercial ),
                         SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.vlr_liquido,0)), '+', NVL(ftnfpr00.vlr_liquido,0), 0)) ) , 
SUM( DECODE( ftcofi00.nf_prodgratis, 'S', NVL(ftnfpr00.qtde_duzias,0), 0 ) ) , 
SUM( DECODE( ftcofi00.nf_prodgratis, 'S', NVL(ftnfpr00.vlr_liquido,0), 0 ) ) 
FROM 
ftprod00 , 
ftnfco00 , 
ftcgma00 , 
ftcgca00 , 
ftspro00 , 
ftclcr00 , 
gsames00 , 
ftcofi00 , 
ftrepr00 , 
gsesta00 , 
ftsupv00 , 
ftgrep00 , 
ftclgr00 , 
ftband00 , 
fttcli00 , 
ftredc00 , 
ftnfpr00 
WHERE 
ftnfco00.emp = 909 AND 
ftnfpr00.fil IN ('101') AND 
ftnfco00.situacao_nf = 'N' AND 
TO_CHAR(ftnfco00.data_emissao,'YYYYMM') >= '200208' AND 
TO_CHAR(ftnfco00.data_emissao,'YYYYMM') <= '200304' AND 
ftcofi00.grupo_faturamento >= '01' AND 
(ftcofi00.atual_fatura IN ('+','-') OR ftcofi00.nf_prodgratis = 'S') AND 
ftcgma00.emp    = ftprod00.emp AND 
ftcgma00.fil    = ftprod00.fil AND 
ftcgma00.codigo = ftprod00.cla_marca AND 
ftcgca00.emp    = ftprod00.emp AND 
ftcgca00.fil    = ftprod00.fil AND 
ftcgca00.codigo = ftprod00.cla_categoria AND 
ftspro00.emp    = ftprod00.emp AND 
ftspro00.fil    = ftprod00.fil AND 
ftspro00.codigo = ftprod00.situacao AND 
ftclcr00.emp           = ftnfco00.emp AND 
ftclcr00.fil           = ftnfco00.empfil AND 
ftclcr00.tipo_cadastro = ftnfco00.tipo_cad_clicre AND 
ftclcr00.codigo        = ftnfco00.cod_cliente AND 
gsames00.ano_mes       = TO_CHAR(ftnfco00.data_emissao,'YYYYMM') AND 
ftcofi00.emp           = ftnfco00.emp AND 
ftcofi00.fil           = ftnfco00.empfil AND 
ftcofi00.codigo_fiscal = ftnfco00.cod_fiscal AND 
ftrepr00.emp           = ftnfco00.emp AND 
ftrepr00.fil           = ftnfco00.empfil AND 
ftrepr00.codigo_repr   = ftnfco00.cod_repres AND 
gsesta00.estado_sigla  = ftnfco00.estado_cliente AND 
ftsupv00.emp               = ftrepr00.emp AND 
ftsupv00.fil               = ftrepr00.fil AND 
ftsupv00.codigo_supervisor = ftrepr00.codigo_supervisor AND 
ftgrep00.emp               = ftrepr00.emp AND 
ftgrep00.fil               = ftrepr00.fil AND 
ftgrep00.codigo_grupo_rep  = ftrepr00.codigo_grupo_rep AND 
ftclgr00.emp               = ftclcr00.emp AND 
ftclgr00.fil               = ftclcr00.fil AND 
ftclgr00.codigo            = ftclcr00.codigo_grupo_cliente AND 
ftband00.emp               = ftclcr00.emp AND 
ftband00.fil               = ftclcr00.fil AND 
ftband00.codigo            = ftclcr00.bandeira_cliente AND 
fttcli00.emp               = ftclcr00.emp AND 
fttcli00.fil               = ftclcr00.fil AND 
fttcli00.cod_tipocliente   = ftclcr00.codigo_tipo_cliente AND 
ftredc00.emp               = ftclcr00.emp AND 
ftredc00.fil               = ftclcr00.fil AND 
ftredc00.tipo_contribuinte = ftclcr00.tipo_contribuinte AND 
ftredc00.codigo_rede       = ftclcr00.codigo_rede AND 
gsesta00.estado_sigla      = ftclcr00.emp_estado AND 
ftnfco00.emp           = ftnfpr00.emp AND 
ftnfco00.fil           = ftnfpr00.fil AND 
ftnfco00.nota_fiscal   = ftnfpr00.nota_fiscal AND 
ftnfco00.serie         = ftnfpr00.serie AND 
ftnfco00.data_emissao  = ftnfpr00.data_emissao AND 
ftprod00.emp           = ftnfpr00.emp AND 
ftprod00.fil           = ftnfpr00.empfil AND 
ftprod00.tipo_cadastro = ftnfpr00.tipo_cad_promat AND 
ftprod00.codigo_produto= ftnfpr00.cod_produto 
GROUP BY 
ftnfco00.estado_cliente , 
ftcofi00.grupo_faturamento , 
ftprod00.tipo_cadastro||ftprod00.codigo_produto||'||'||gsames00.ano_mes , 
ftprod00.descricao_produto||'||'||gsames00.descricao
 

I have created the decode, NVL and DIVIDE functions.... the problem is that the 
where condition makes this query to slow ( about 4 min ) and the same query in 
my Oracle database takes less than 40 seconds. I have tried to isolate the 
problem taking off some  fields and I left justa the two first fields in the 
query ( ftnfco00.estado_cliente , ftcofi00.grupo_faturamento ) and it still 
taking  almost 4 min to return. Does anyone have a hint to give me to make it 
faster ?

    Atached goes a explain analyze return os this query.




Atenciosamente,

Rhaoni Chiu Pereira
Sistêmica Computadores

Visite-nos na Web: http://sistemica.info
Fone/Fax : +55 51 3328 1122





                                                                     QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=7971.65..7971.68 rows=1 width=833) (actual 
time=1501529.16..1508066.32 rows=1755 loops=1)
   ->  Group  (cost=7971.65..7971.66 rows=1 width=833) (actual 
time=1501495.07..1502517.99 rows=24111 loops=1)
         ->  Sort  (cost=7971.65..7971.65 rows=1 width=833) (actual 
time=1501495.03..1501563.87 rows=24111 loops=1)
               Sort Key: ftnfco00.estado_cliente, ftcofi00.grupo_faturamento, 
((((ftprod00.tipo_cadastro)::text || (ftprod00.codigo_produto)::text) || '||'::text) 
|| (gsames00.ano_mes)::text), ((ftprod00.descricao_produto || '||'::character varying) 
|| gsames00.descricao)
               ->  Nested Loop  (cost=7913.31..7971.64 rows=1 width=833) (actual 
time=1382281.24..1495748.79 rows=24111loops=1)
                     Join Filter: ("inner".estado_sigla = "outer".estado_cliente)
                     ->  Nested Loop  (cost=7913.31..7969.90 rows=1 width=827) (actual 
time=1382271.41..1490702.11 rows=24111 loops=1)
                           Join Filter: (("outer".emp = "inner".emp) AND ("inner".fil 
= "outer".empfil) AND ("inner".codigo = "outer".bandeira_cliente))
                           ->  Nested Loop  (cost=7913.31..7968.87 rows=1 width=795) 
(actual time=1382179.80..1486492.69 rows=24111 loops=1)
                                 Join Filter: (("inner".emp = "outer".emp) AND 
("outer".codigo = "inner".cla_categoria)AND ("outer".codigo = "inner".cla_marca) AND 
("outer".codigo = "inner".situacao))
                                 ->  Nested Loop  (cost=7913.31..7954.34 rows=3 
width=705) (actual time=1381632.57..1464651.32 rows=241110 loops=1)
                                       Join Filter: (("outer".emp = "inner".emp) AND 
("inner".fil = "outer".empfil))                                       ->  Nested Loop  
(cost=7913.31..7941.84 rows=2 width=665) (actual time=1381444.87..1408531.29 
rows=241110 loops=1)
                                             Join Filter: (("inner".emp = "outer".emp) 
AND ("outer".fil = "inner".empfil) AND("outer".codigo_grupo_rep = 
"inner".codigo_grupo_rep))
                                             ->  Seq Scan on ftgrep00  
(cost=0.00..1.01 rows=1 width=32) (actual time=28.97..28.97 rows=1 loops=1)
                                             ->  Materialize  (cost=7940.79..7940.79 
rows=2 width=633) (actual time=1381415.51..1385445.39 rows=241110 loops=1)
                                                   ->  Nested Loop  
(cost=7913.31..7940.79 rows=2 width=633) (actual time=1348400.96..1379090.93 
rows=241110 loops=1)
                                                         Join Filter: (("inner".emp = 
"outer".emp) AND ("outer".fil = "inner".empfil) AND ("outer".codigo_supervisor = 
"inner".codigo_supervisor))
                                                         ->  Seq Scan on ftsupv00  
(cost=0.00..1.01 rows=1 width=32) (actual time=13.92..13.94 rows=1 loops=1)
                                                         ->  Materialize  
(cost=7939.75..7939.75 rows=2 width=601) (actual time=1348386.88..1357853.77 
rows=241110 loops=1)
                                                               ->  Nested Loop  
(cost=7913.31..7939.75 rows=2 width=601) (actual time=238548.84..1344339.33 
rows=241110 loops=1)
                                                                     Join Filter: 
(("inner".ano_mes)::text = to_char("outer".data_emissao, 'YYYYMM'::text))
                                                                     ->  Merge Join  
(cost=7913.31..7913.45 rows=2 width=576)(actual time=238524.31..275724.44 rows=241110 
loops=1)
                                                                           Merge Cond: 
(("outer".empfil = "inner".fil) AND ("outer".codigo_tipo_cliente = 
"inner".cod_tipocliente) AND ("outer".emp = "inner".emp))
                                                                           ->  Sort  
(cost=7911.79..7911.80 rows=2 width=543)(actual time=238523.48..254284.13 rows=241110 
loops=1)
                                                                                 Sort 
Key: ftclcr00.fil, ftclcr00.codigo_tipo_cliente, ftnfpr00.emp
                                                                                 ->  
Nested Loop  (cost=7851.29..7911.78 rows=2 width=543) (actual time=80005.55..144755.84 
rows=241110 loops=1)
                                                                                       
Join Filter: (("inner".emp_estado = "outer".estado_cliente) AND ("outer".codigo = 
"inner".codigo_grupo_cliente))
                                                                                       
->  Nested Loop  (cost=7851.29..7853.22 rows=10 width=449) (actual 
time=79960.48..94206.17 rows=241110 loops=1)
                                                                                       
      Join Filter: (("inner".emp = "outer".emp) AND ("outer".fil = "inner".empfil))
                                                                                       
      ->  Seq Scan on ftclgr00  (cost=0.00..1.01 rows=1 width=32) (actual 
time=12.21..12.21 rows=1 loops=1)                                                      
                                       
->  Materialize  (cost=7852.06..7852.06 rows=10 width=417) (actual 
time=79948.16..82051.01 rows=241110 loops=1)
                                                                                       
            
->  Merge Join  (cost=7851.29..7852.06 rows=10 width=417) (actual 
time=65536.22..78515.30 rows=241110 loops=1)
                                                                                       
                
Merge Cond: (("outer".empfil = "inner".fil) AND ("outer".cod_repres = 
"inner".codigo_repr) AND ("outer".emp = "inner".emp))
                                                                                       
                  
->  Sort  (cost=7840.51..7840.54 rows=10 width=364) (actual time=64203.33..65522.80 
rows=241110 loops=1)
                                                                                       
                        
Sort Key: ftnfco00.empfil, ftnfco00.cod_repres, ftnfpr00.emp
                                                                                       
                        
->  Nested Loop
  (cost=7569.51..7840.35 rows=10 width=364) (actual time=2094.38..31469.51 rows=241110 
loops=1)
                                                                                       
                              
Join Filter: (("outer".emp = "inner".emp) AND ("outer".empfil = "inner".fil))
                                                                                       
                              
->  Nested Loop  (cost=7569.51..7839.10 rows=1 width=337) (actual 
time=2094.00..21568.08 rows=24111 loops=1)                                             
                                                                        
Join Filter: (("outer".emp = "inner".emp) AND ("outer".empfil = "inner".fil))
                                                                                       
                                   
-> Nested Loop  (cost=7569.51..7838.07 rows=1 width=305) (actual 
time=2086.59..20456.24 rows=24111 loops=1)
   Join Filter: (("outer".emp = "inner".emp) AND ("outer".empfil = "inner".fil))
   ->  Nested Loop  (cost=7569.51..7837.05 rows=1 width=273) (actual 
time=2078.21..19368.42 rows=24111 loops=1)
         ->  Merge Join  (cost=7569.51..7572.07 rows=44 width=161) (actual 
time=1991.00..2262.54 rows=7718 loops=1)
               Merge Cond: (("outer".cod_fiscal = "inner".codigo_fiscal) AND 
("outer".empfil = "inner".fil) AND ("outer".emp = "inner".emp))
               ->  Sort  (cost=7520.71..7521.45 rows=295 width=109) (actual 
time=1958.40..1994.38 rows=9339 loops=1)
                     Sort Key: ftnfco00.cod_fiscal, ftnfco00.empfil, ftnfco00.emp
                     ->  Seq Scan on ftnfco00  (cost=0.00..7508.60 rows=295 width=109) 
(actual time=14.50..1581.81 rows=9339 loops=1)
                           Filter: ((emp = 909::numeric) AND (situacao_nf = 
'N'::character varying) AND (to_char(data_emissao, 'YYYYMM'::text) >= '200209'::text) 
AND (to_char(data_emissao, 'YYYYMM'::text) <= '200304'::text))
               ->  Sort  (cost=48.80..48.98 rows=72 width=52) (actual 
time=31.86..43.69 rows=7765 loops=1)
                     Sort Key: ftcofi00.codigo_fiscal, ftcofi00.fil, ftcofi00.emp
                     ->  Seq Scan on ftcofi00  (cost=0.00..46.58 rows=72 width=52) 
(actual time=7.62..31.07 rows=75 loops=1)
                           Filter: ((grupo_faturamento >= '01'::character varying) AND 
((atual_fatura = '+'::character varying) OR (atual_fatura = '-'::character varying) OR 
(nf_prodgratis = 'S'::character varying)))
         ->  Index Scan using ftnfpr00_pkey on ftnfpr00  (cost=0.00..5.95 rows=1 
width=112) (actual time=1.41..2.14 rows=3 loops=7718)
               Index Cond: (("outer".emp = ftnfpr00.emp) AND ("outer".fil = 
ftnfpr00.fil) AND (ftnfpr00.fil = 101::numeric) AND ("outer".nota_fiscal = 
ftnfpr00.nota_fiscal) AND ("outer".serie = ftnfpr00.serie) AND ("outer".data_emissao = 
ftnfpr00.data_emissao))
   ->  Seq Scan on ftcgca00  (cost=0.00..1.01 rows=1 width=32) (actual time=0.01..0.01 
rows=1 loops=24111)                                                                    
                                                       
-> Seq Scan on ftcgma00  (cost=0.00..1.01 rows=1 width=32) (actual time=0.01..0.01 
rows=1 loops=24111)                                                                    
                                                 
->  Seq Scan on ftspro00  (cost=0.00..1.10 rows=10 width=27) (actual time=0.00..0.04 
rows=10 loops=24111)                                                                   
                                      
->  Sort  (cost=10.78..11.05 rows=109 width=53) (actual time=1332.54..1510.69 
rows=241175 loops=1)                                                                   
                                            
Sort Key: ftrepr00.fil, ftrepr00.codigo_repr, ftrepr00.emp                             
                                                                                  
->  Seq Scan on ftrepr00  (cost=0.00..7.09 rows=109 width=53) (actual 
time=406.63..1331.33 rows=109 loops=1)                                                 
                                      
->  Index Scan using ftclcr01 on ftclcr00  (cost=0.00..5.83 rows=1 width=94) (actual 
time=0.13..0.14 rows=1 loops=241110)                                                   
                                          
Index Cond: ((ftclcr00.emp = "outer".emp) AND (ftclcr00.fil = "outer".empfil) AND 
(ftclcr00.tipo_cadastro = "outer".tipo_cad_clicre) AND (ftclcr00.codigo = "ou
ter".cod_cliente))
                                                                           ->  Sort  
(cost=1.52..1.56 rows=17 width=33) (actual time=0.61..225.43 rows=240986 loops=1)      
                                                                           
Sort Key: fttcli00.fil, fttcli00.cod_tipocliente, fttcli00.emp                         
                                                        
->  Seq Scan on fttcli00  (cost=0.00..1.17 rows=17 width=33) (actual time=0.12..0.17 
rows=17 loops=1)
                                                                     ->  Seq Scan on 
gsames00  (cost=0.00..6.72 rows=372 width=25) (actual time=0.00..1.28 rows=372 
loops=241110)
                                       ->  Index Scan using ftredc00_pkey on ftredc00  
(cost=0.00..5.83 rows=1 width=40) (actual time=0.12..0.12 rows=1 loops=241110)
                                             Index Cond: ((ftredc00.emp = "outer".emp) 
AND (ftredc00.fil = "outer".fil) AND (f
tredc00.tipo_contribuinte = "outer".tipo_contribuinte) AND (ftredc00.codigo_rede = 
"outer".codigo_rede))
                                 ->  Index Scan using ftprod00_pkey on ftprod00  
(cost=0.00..5.74 rows=1 width=90) (actual time=0.06..0.06 rows=1 loops=241110)
                                       Index Cond: ((ftprod00.emp = "outer".emp) AND 
(ftprod00.fil = "outer".empfil) AND (ftprod00.tipo_cadastro = "outer".tipo_cad_promat) 
AND (ftprod00.codigo_produto = "outer".cod_produto))
                           ->  Seq Scan on ftband00  (cost=0.00..1.01 rows=1 width=32) 
(actual time=0.01..0.01 rows=1 loops=24111)
                     ->  Seq Scan on gsesta00  (cost=0.00..1.33 rows=33 width=6) 
(actual time=0.01..0.08 rows=33 loops=24111) Total runtime: 1508259.91 msec
(74 rows)
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to