Re: firebird X postgresql 8.1.2 windows, performance comparison

On 3/14/06, andremachado <andremachado(at)techforce(dot)com(dot)br> wrote:
> Hello,
> Attached is a file containing the problematic queries cited yesterday, with
> "explain", "\di" and "show all" outputs.
> The first one finished in almost 4 hours. Firebird for windows finished in 1m30s.
> The second one CRASHED after some hours, without finishing. The error message
> is at the file too.
> I will ask my friend to reduce shared_buffers to 16000 as this number gave the
> best results for his machine.
> Do you have any suggestion?
> Regards.
> Andre Felipe Machado

Are you looking for help optimizing the postgresql database generally
or for help making those queries run faster?

1. do all basic stuff. (analyze, etc etc)

2. for first query, try rewriting without explicit join
select count(distinct NF.ID_NF) as contagem,
DE.AM_REFERENCIA as campo
from DECLARACAO DE, CADASTRO CAD, NOTA_FISCAL NF, EMPRESA EMP,
ARQUIVO_PROCESSADO ARQ
where CAD.ID_DECLARACAO=DE.ID_DECLARACAO and
NF.ID_CADASTRO=CAD.ID_CADASTRO and
EMP.ID_EMPRESA=DE.ID_EMPRESA and
ARQ.ID_ARQUIVO=DE.ID_ARQUIVO
group by DE.AM_REFERENCIA order by DE.AM_REFERENCIA ;

3. second query is a mess. remove try removing explicit joins and
replace 'where in' with 'where exists'

4. your tables look like classic overuse of surrogate keys. Do some
experimentation with natural keys to reduce the number of joins
involved.

Merlin