pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rhaoni Chiu Pereira <rhaoni@sistemica.info>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Query too slow
Date: Mon, 25 Aug 2003 12:03:12 -0300
Message-ID: <1061823792.3f4a253015fdc@sistemica.info> (raw)

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

Attachments:

explain.sql (text/plain, 12.6K) download 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)

view thread (8+ messages)  latest in thread

Message-ID: <1061823792.3f4a253015fdc@sistemica.info>
Permalink:  ../1061823792.3f4a253015fdc@sistemica.info/
Also on:    postgresql.org/message-id/1061823792.3f4a253015fdc@sistemica.info

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: rhaoni@sistemica.info
  Subject: Re: Query too slow
  In-Reply-To: <1061823792.3f4a253015fdc@sistemica.info>
  References: <1061823792.3f4a253015fdc@sistemica.info>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox