pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Merlin Moncure <mmoncure@gmail.com>
To: andremachado <andremachado@techforce.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: firebird X postgresql 8.1.2 windows, performance comparison
Date: Tue, 14 Mar 2006 14:36:25 -0500
Message-ID: <b42b73150603141136n451ddb8bwa93e025ce283af91@mail.gmail.com> (raw)
In-Reply-To: <20060314183226.M29726@techforce.com.br>
References: <20060314183226.M29726@techforce.com.br>

On 3/14/06, andremachado <andremachado@techforce.com.br> wrote:
> Unfortunately, the first query simply returned the same estimated costs by the
> planner.

Can you try making a big increase to work_mem .conf parameter (as much
as is reasonalbe) and see if that helps either query?

ok, thats understandable. you do have indexes on all the id columns, yes?

> The second one, using EXISTS, multiplied its cost almost 200 times!

regardless of what the planner said, could you please try running
query with explain analyze? also:
1. DE.ID_ARQUIVO in (10) could be written as DE.ID_ARQUIVO = 10

2.         and CAD.ID_DECLARACAO=DE.ID_DECLARACAO
            and CAD.ID_CADASTRO=NOTA_FISCAL.ID_CADASTRO
could possibly beneift from key on CAD(ID_DECLARACAO, ID_CADASTRO)
also, you could try adding an index on DE(ID_ARQUIVO, ID_DECLARACAO)

3. and (select sum(ITEM_NOTA.VA_TOTAL) from ITEM_NOTA
  where ITEM_NOTA.ID_NF = NOTA_FISCAL.ID_NF) < 999999999999;

this is probably the major performance killer. you have to somehow
optimize the 'sum' out of the target of the major where clause.  One
way to possibly tackle that is to attempt to materialze the sum into
nota_fiscal.

merlin



view thread (23+ messages)  latest in thread

Message-ID: <b42b73150603141136n451ddb8bwa93e025ce283af91@mail.gmail.com>
Permalink:  ../b42b73150603141136n451ddb8bwa93e025ce283af91@mail.gmail.com/
Also on:    postgresql.org/message-id/b42b73150603141136n451ddb8bwa93e025ce283af91@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: mmoncure@gmail.com, andremachado@techforce.com.br
  Subject: Re: firebird X postgresql 8.1.2 windows, performance comparison
  In-Reply-To: <b42b73150603141136n451ddb8bwa93e025ce283af91@mail.gmail.com>
  References: <20060314183226.M29726@techforce.com.br> <b42b73150603141136n451ddb8bwa93e025ce283af91@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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