Обсуждение: firebird X postgresql 8.1.2 windows, performance comparison

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

On 3/14/06, andremachado <andremachado@techforce.com.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

On Tue, Mar 14, 2006 at 09:02:49AM -0300, andremachado 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.

PANIC: could not open file "pg_xlog/0000000100000018000000E7" (log file
24, segment 231): Invalid argument

IIRC that means you have a data corruption issue.

As for the queries, EXPLAIN ANALYZE would be in order here. It looks
like the first one might benefit from increasing work_memory.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461