Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 316DB9FA222 for ; Thu, 11 Jan 2007 19:48:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 74443-10 for ; Thu, 11 Jan 2007 19:48:09 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.229]) by postgresql.org (Postfix) with ESMTP id 233759FB1FB for ; Thu, 11 Jan 2007 19:48:10 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so680003wxd for ; Thu, 11 Jan 2007 15:48:08 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=ZuSbugLMRsLcvySD0+PNU8qAV1WIcRE1qRwrfJSAY46JBjbVf5FX/GoVM0CZrtdi3Y4ry727H6Xc98jbTfO+4O06KCy0kNpqo2yQXQe9AHv+9CQtRcD9TdSSR/LxKjSKxuK8LhG753cDdct03EWdA2NTehuyhqVIOKmr575LhAk= Received: by 10.70.27.18 with SMTP id a18mr24418wxa.1168559288844; Thu, 11 Jan 2007 15:48:08 -0800 (PST) Received: from ?192.168.0.2? ( [200.171.36.30]) by mx.google.com with ESMTP id 34sm1870514wra.2007.01.11.15.48.06; Thu, 11 Jan 2007 15:48:07 -0800 (PST) Message-ID: <45A6CCAC.4000207@gmail.com> Date: Thu, 11 Jan 2007 21:47:33 -0200 From: Cesar Suga User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.0.7) Gecko/20060914 SeaMonkey/1.0.5 MIME-Version: 1.0 To: carlos.reimer@opendb.com.br CC: pgsql-performance@postgresql.org Subject: Re: RES: Improving SQL performance References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/194 X-Sequence-Number: 22795 Hi, Carlos, Wouldn't it be better if you used INT in 'codcep' in both tables (as CEP/ZIP numbers are [0-9]{8})? Casting as Tom Lane suggested is also a good alternative, yet I think it'd be much better if you used int in both columns. Regards, Cesar Let's see the query: SELECT ENDE.* , DEND.DESEND, DEND.USOEND, DEND.DUPEND, to_char('F') as NOVO, LOG.TIPLOG FROM TT_END ENDE LEFT OUTER JOIN TD_END DEND ON DEND.CODTAB = ENDE.TIPEND LEFT OUTER JOIN TT_LOG LOG ON LOG.CODCEP = ENDE.CODCEP||CODLOG WHERE ENDE.FILCLI = '001' AND ENDE.CODCLI = ' 19475'; Carlos H. Reimer wrote: > Yes, I do have an index on tt_log.codcep. > > Indexes I´ve on both tables: > > tt_end > Indexes: > "pk_end" PRIMARY KEY, btree (filcli, codcli, codfil, numend) > "ak_end_numdoc" UNIQUE, btree (numdoc) > "i_fk_end_darc" btree (codarc, tiparc) > "i_fk_end_dend" btree (tipend) > "i_fk_end_dfil" btree (codfil) > "i_fk_end_dreg" btree (regiao) > "i_fk_end_mun" btree (codcid) > tt_log > Indexes: > "i_fk_log_bai" btree (codbai) > "i_lc_log_codcep" btree (codcep) > > Any clue? > > Thanks! > > Reimer > > > >> -----Mensagem original----- >> De: Tom Lane [mailto:tgl@sss.pgh.pa.us] >> Enviada em: quinta-feira, 11 de janeiro de 2007 16:31 >> Para: carlos.reimer@opendb.com.br >> Cc: pgsql-performance@postgresql.org >> Assunto: Re: [PERFORM] Improving SQL performance >> >> >> "Carlos H. Reimer" writes: >> >>> I know that the problem with the following SQL is the "LOG.CODCEP = >>> ENDE.CODCEP||CODLOG" condition, but what can I >>> do to improve the performance? >>> >> Seems the problem is not using an index for tt_log. Do you have an >> index on tt_log.codcep? If so, maybe you need to cast the result of >> the concatenation to char(8) to get it to use the index. >> >> regards, tom lane >> >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > >