X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0CBE0D1D5D9 for ; Mon, 8 Mar 2004 16:05:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 38002-04 for ; Mon, 8 Mar 2004 12:05:28 -0400 (AST) Received: from fep02-mail.bloor.is.net.cable.rogers.com (fep02-mail.bloor.is.net.cable.rogers.com [66.185.86.72]) by svr1.postgresql.org (Postfix) with ESMTP id 049B5D1D550 for ; Mon, 8 Mar 2004 12:05:27 -0400 (AST) Received: from phlogiston.dydns.org ([65.49.121.67]) by fep02-mail.bloor.is.net.cable.rogers.com (InterMail vM.5.01.05.12 201-253-122-126-112-20020820) with ESMTP id <20040308160445.ECBO434741.fep02-mail.bloor.is.net.cable.rogers.com@phlogiston.dydns.org> for ; Mon, 8 Mar 2004 11:04:45 -0500 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id 309833F40; Mon, 8 Mar 2004 11:05:25 -0500 (EST) Date: Mon, 8 Mar 2004 11:05:25 -0500 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: Using bigint needs explicit cast to use the index Message-ID: <20040308160525.GA25077@phlogiston.dyndns.org> Mail-Followup-To: pgsql-performance@postgresql.org References: <001101c404a3$fb34d320$fd08a8c0@steve> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <001101c404a3$fb34d320$fd08a8c0@steve> User-Agent: Mutt/1.5.4i X-Authentication-Info: Submitted using SMTP AUTH LOGIN at fep02-mail.bloor.is.net.cable.rogers.com from [65.49.121.67] using ID at Mon, 8 Mar 2004 11:04:44 -0500 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/98 X-Sequence-Number: 5954 On Mon, Mar 08, 2004 at 10:26:21AM +1000, Steven Butler wrote: > I tested my hunch by casting the constant to bigint (as can be seen below) > and suddenly the query is using the index again. Yes. You can make this work all the time by quoting the constant. That is, instead of WHERE indexcolumn = 123 do WHERE indexcolumn = '123' > We are currently using pg 7.3.4. Is this intended behaviour? Should the > constant be cast to the type of the table column where possible, or should "Intended", no. "Expected", yes. This topic has had the best Postgres minds work on it, and so far nobody's come up with a solution. There was a proposal to put in a special-case automatic fix for int4/int8 in 7.4, but I don't know whether it made it in. A -- Andrew Sullivan | ajs@crankycanuck.ca I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin