Thread: SQL performance problems

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

Rhaoni Chiu Pereira <rhaoni@sistemica.info> writes:
>    I still have performance problems with this sql:

It seems odd that all the joins are being done as nestloops.  Perhaps
you shouldn't be forcing enable_seqscan off?

            regards, tom lane

    Hi,

    Estimated and actual rows differ a lot. Did you a VACUUM ANALYZE so
that the optimizer could update its statistics?

    Also it would be great if you could provide more information, as your
PostgreSQL version, your table and indexes descriptions, etc. Have a
look at:

http://techdocs.postgresql.org/guides/SlowQueryPostingGuidelines

    Please excuse me if you have already done it. I had a quick look at the
list's archives and didn't found it in your prior posts.

    Regards,
--
Alberto Caso Palomino
Adaptia Soluciones Integrales
http://www.adaptia.net
alberto.caso@adaptia.net

On Mon, 01-09-2003 at 13:42, Rhaoni Chiu Pereira wrote:
> I've done that but it didn't make much difference.
> Do you know some documentation on explain ? I don't understand the results..
>

http://developer.postgresql.org/docs/postgres/sql-explain.html
http://developer.postgresql.org/docs/postgres/performance-tips.html

Also this list's archives (which can be found at
http://archives.postgresql.org/pgsql-performance/ ) are a good source
of info on the subject.

Best Regards,

--
Alberto Caso Palomino
Adaptia Soluciones Integrales
http://www.adaptia.net
alberto.caso@adaptia.net