X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id CDB9E9DCDBB for ; Tue, 14 Mar 2006 15:36:27 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 98647-09 for ; Tue, 14 Mar 2006 15:36:28 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.196]) by postgresql.org (Postfix) with ESMTP id BCD509DCC94 for ; Tue, 14 Mar 2006 15:36:25 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i23so2320479wra for ; Tue, 14 Mar 2006 11:36:25 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=esR5YMKJPBeSogkE3VM75XBfefigYAb0DW9XYwBPo+ljKcms3BzD0AkDoFP8zQsc5fk2yeNYViox5R5/1kauMxC3uwfPWimkUQNBZ13BwSprNnUku/KLK7hKoxgLjieT1TBOKYuJkZ4s9PhduvkKJZrrSLCEp7XmDL4PvmTNFoQ= Received: by 10.65.59.17 with SMTP id m17mr2794377qbk; Tue, 14 Mar 2006 11:36:25 -0800 (PST) Received: by 10.65.137.20 with HTTP; Tue, 14 Mar 2006 11:36:25 -0800 (PST) Message-ID: Date: Tue, 14 Mar 2006 14:36:25 -0500 From: "Merlin Moncure" To: andremachado Subject: Re: firebird X postgresql 8.1.2 windows, performance comparison Cc: pgsql-performance@postgresql.org In-Reply-To: <20060314183226.M29726@techforce.com.br> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <20060314183226.M29726@techforce.com.br> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.142 required=5 tests=[AWL=0.142] X-Spam-Score: 0.142 X-Spam-Level: X-Archive-Number: 200603/194 X-Sequence-Number: 17631 On 3/14/06, andremachado wrote: > Unfortunately, the first query simply returned the same estimated costs b= y 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 =3D 10 2. and CAD.ID_DECLARACAO=3DDE.ID_DECLARACAO and CAD.ID_CADASTRO=3DNOTA_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 =3D 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