Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2D4629FA19A for ; Mon, 30 Oct 2006 10:28:01 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 31395-02 for ; Mon, 30 Oct 2006 10:27:56 -0400 (AST) X-Greylist: delayed 02:47:51.812677 by SQLgrey- Received: from mxfep03.bredband.com (mxfep03.bredband.com [195.54.107.76]) by postgresql.org (Postfix) with ESMTP id 5C9A99FB1F0 for ; Mon, 30 Oct 2006 10:27:55 -0400 (AST) Received: from ironport2.bredband.com ([195.54.107.84] [195.54.107.84]) by mxfep03.bredband.com with ESMTP id <20061030142753.GMOG25623.mxfep03.bredband.com@ironport2.bredband.com> for ; Mon, 30 Oct 2006 15:27:53 +0100 Received: from c-a009e155.242-5-64736c11.cust.bredbandsbolaget.se (HELO server.kregert.se) ([85.225.9.160]) by ironport2.bredband.com with ESMTP/TLS/AES256-SHA; 30 Oct 2006 15:27:52 +0100 Received: from knatte (knatte.kregert.se [10.10.0.9]) by server.kregert.se (8.13.6/8.13.4) with SMTP id k9UENpL1006506; Mon, 30 Oct 2006 15:23:51 +0100 Message-ID: <003301c6fc2f$57e2c660$09000a0a@kregert.se> From: "Mattias Kregert" To: "Steinar H. Gunderson" Cc: References: <200610301205.08087.mattias@kregert.se> <20061030122733.GA6357@uio.no> Subject: Re: Strange plan in pg 8.1.0 Date: Mon, 30 Oct 2006 15:26:09 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="utf-8"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.292 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/610 X-Sequence-Number: 21739 >>> From: "Steinar H. Gunderson" > On Mon, Oct 30, 2006 at 01:05:07PM +0200, Mattias Kregert wrote: >> -> Hash Left Join (cost=59.66..206763.11 rows=1215336 >> width=27) (actual time=4.959..3228.550 rows=1216434 loops=1) >> Hash Cond: (("outer".prislista = ("inner".listid)::text) >> AND ("outer".tjanst = ("inner".tjanstid)::text)) > > Note the conversion to text here. Are you sure the types are matching on > both > sides of the join? > > /* Steinar */ On the left side it is text, and on the right side it is varchar(10). Casting left side to varchar(10) does not help, in fact it makes things even worse: The cast to ::text vanishes in a puff of logic, but the plan gets bigger and even slower (20-25 seconds). A RIGHT join takes only 20 milliseconds, but i want the left join because there could be missing rows in the "prislist" table... /* m */