X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 30FA7D1C4CC for ; Fri, 25 Jul 2003 19:39:42 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 68499-03 for ; Fri, 25 Jul 2003 16:38:54 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 3C8EDD1C4BC for ; Fri, 25 Jul 2003 16:39:40 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h6PJdcQ3027751; Fri, 25 Jul 2003 15:39:39 -0400 (EDT) To: Stephan Szabo Cc: Elielson Fontanezi , pgsql-sql , pgsql-general Subject: Re: Query analyse In-reply-to: <20030725084243.U31713-100000@megazone.bigpanda.com> References: <20030725084243.U31713-100000@megazone.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Fri, 25 Jul 2003 08:49:15 -0700" Date: Fri, 25 Jul 2003 15:39:38 -0400 Message-ID: <27750.1059161978@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/1250 X-Sequence-Number: 46103 Stephan Szabo writes: > You're probably ending up with different plans since in one case it has > a plain column reference and in the other it has a marginally complicated > expression in the join condition. Yeah. 7.3 and before cannot do merge or hash joins on conditions that are any more complex than "var = var". The query with the trunc() is undoubtedly falling back to the stupidest kind of nestloop. > As something to try, perhaps make a function that returns > trunc($1/100000.0)*100000 and index on that function for the column and > see if that changes the plan you get. It might help --- you might possibly get a nestloop-with-inner-indexscan out of that. Not sure though, since the planner is likely to be using bad guesstimates about the selectivity of the expression. 7.4 should do better on this. regards, tom lane