Received: from ece.rice.edu (ece.rice.edu [128.42.4.34]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAFHTbm22827; Thu, 15 Nov 2001 12:29:37 -0500 (EST) (envelope-from reedstrm@rice.edu) Received: from localhost (localhost [127.0.0.1]) by ece.rice.edu (Postfix) with ESMTP id 0F66E68A5B; Thu, 15 Nov 2001 11:29:42 -0600 (CST) Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by ece.rice.edu (Postfix) with ESMTP id 59E9468A17; Thu, 15 Nov 2001 11:29:41 -0600 (CST) Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.22 #1 (Debian)) id 164QKb-0000tD-00; Thu, 15 Nov 2001 11:29:41 -0600 Date: Thu, 15 Nov 2001 11:29:41 -0600 From: "Ross J. Reedstrom" To: pgsql-sql@postgresql.org Cc: pgsql-hackers@postgresql.org Subject: Re: problem: index on number not honoured Message-ID: <20011115112941.B2518@rice.edu> Mail-Followup-To: "Ross J. Reedstrom" , pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org References: <9spaa8$inv$1@news.tht.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <9spaa8$inv$1@news.tht.net> User-Agent: Mutt/1.3.18i X-Virus-Scanned: by AMaViS snapshot-20010714 X-Archive-Number: 200111/222 X-Sequence-Number: 5605 This is an instance of a known problem: numeric constants get resolved to 'float8' during the parsing stage, so the planner doesn't know it can use the 'int2' (or whatever) index for this query. The 'string like' constants (i.e. anything quoted with ') are kept as 'unknown' until the last stages, when the planner can then attempt to resolve them to match the type of the underlying column/index. It is interesting to see your comments regarding the JDBC interface: I haven't seen that angle reported before. To date, it's been a matter of suggesting workarounds to individual cases. The correct solution i(which has arisen out of discussion on the HACKERS list: I think it's Tom Lane's idea) is probably to come up with the concept of an 'unknown numeric' constant, and treat it like the string constant gets treated. We've seen a rash of these problems recently: unfortunately, this isn't fixed in 7.2 (which is in Beta 2 right now: should go Release Candidate1 tomorrow), but I'd suggest it's a candidate for fixing early in 7.3. Might even be argued as a 'bug fix', but the changes needed to fix this right are probably so extensive that it'll never go into a stable release.