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

Hello,
Many thanks for your suggestions.
I am trying to optimize server configs, as (presumed) my friend already
optimized his queries and firebird windows is executing them fast.
You could see at the new attached file the results of the queries rewrite.
Unfortunately, the first query simply returned the same estimated costs by the
planner.
The second one, using EXISTS, multiplied its cost almost 200 times!
"Exists" is painfully slow.
The shared_buffers was reduced again.
Do you have any suggestions?
Many thanks.
Andre Felipe Machado

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