Skip site navigation (1) Skip section navigation (2)

SQL performance problems

From: Rhaoni Chiu Pereira <rhaoni(at)sistemica(dot)info>
To: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: SQL performance problems
Date: 2003-08-29 20:59:19
Message-ID: 1062190759.3f4fbea7aae8c@sistemica.info (view raw or flat)
Thread:
Lists: pgsql-performance
Hi List,


   I still have performance problems with this sql:

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 ,
ftprod00.descricao_produto ,
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') <= '200303' 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 ,
ftprod00.descricao_produto

I have created some oracle function in the database 'cuz I want the same
alication to use both Oracle or PostgreSQL without changing any source.

atached follow tha explain analyze for this query and my postgresql.conf.

I still searching a way to make it faster. I've tried to change a lot of
variables values like sort_mem, effective_cache_size, fsync, ...
I change the machine box from a Pentium III 1Ghz  with 256 RAM to a P4 1.7 with
512 RAM DDR.
   I don't know what else to do !

Atenciosamente,

Rhaoni Chiu Pereira
Sistêmica Computadores

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






Attachment: explain2.sql
Description: application/octet-stream (13.5 KB)
Attachment: postgresql.conf
Description: application/octet-stream (5.1 KB)

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2003-08-29 22:03:48
Subject: Re: SQL performance problems
Previous:From: Andrew SullivanDate: 2003-08-29 18:51:09
Subject: Re: sourcecode for newly release eRServer?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group