Re: firebird X postgresql 8.1.2 windows, performance comparison

On 3/14/06, andremachado <andremachado(at)techforce(dot)com(dot)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