pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL performance problems
4+ messages / 3 participants
[nested] [flat]
* SQL performance problems
@ 2003-08-29 20:59 Rhaoni Chiu Pereira <rhaoni@sistemica.info>
2003-08-29 22:03 ` Re: SQL performance problems Tom Lane <tgl@sss.pgh.pa.us>
2003-08-31 22:16 ` Re: SQL performance problems Alberto Caso <alberto.caso@adaptia.net>
0 siblings, 2 replies; 4+ messages in thread
From: Rhaoni Chiu Pereira @ 2003-08-29 20:59 UTC (permalink / raw)
To: 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
Attachments:
postgresql.conf (application/octet-stream, 5.1K) download
explain2.sql (application/octet-stream, 13.5K) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SQL performance problems
2003-08-29 20:59 SQL performance problems Rhaoni Chiu Pereira <rhaoni@sistemica.info>
@ 2003-08-29 22:03 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-08-29 22:03 UTC (permalink / raw)
To: Rhaoni Chiu Pereira <rhaoni@sistemica.info>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SQL performance problems
2003-08-29 20:59 SQL performance problems Rhaoni Chiu Pereira <rhaoni@sistemica.info>
@ 2003-08-31 22:16 ` Alberto Caso <alberto.caso@adaptia.net>
2003-09-01 12:11 ` Re: SQL performance problems Alberto Caso <alberto.caso@adaptia.net>
1 sibling, 1 reply; 4+ messages in thread
From: Alberto Caso @ 2003-08-31 22:16 UTC (permalink / raw)
To: Rhaoni Chiu Pereira <rhaoni@sistemica.info>; +Cc: pgsql-performance
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
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SQL performance problems
2003-08-29 20:59 SQL performance problems Rhaoni Chiu Pereira <rhaoni@sistemica.info>
2003-08-31 22:16 ` Re: SQL performance problems Alberto Caso <alberto.caso@adaptia.net>
@ 2003-09-01 12:11 ` Alberto Caso <alberto.caso@adaptia.net>
0 siblings, 0 replies; 4+ messages in thread
From: Alberto Caso @ 2003-09-01 12:11 UTC (permalink / raw)
To: Rhaoni Chiu Pereira <rhaoni@sistemica.info>; +Cc: pgsql-performance
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
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-09-01 12:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-29 20:59 SQL performance problems Rhaoni Chiu Pereira <rhaoni@sistemica.info>
2003-08-29 22:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-31 22:16 ` Alberto Caso <alberto.caso@adaptia.net>
2003-09-01 12:11 ` Alberto Caso <alberto.caso@adaptia.net>
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