X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 67284D1B4B7 for ; Wed, 25 Feb 2004 19:18:29 +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 88328-09 for ; Wed, 25 Feb 2004 15:18:27 -0400 (AST) Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net [194.217.242.92]) by svr1.postgresql.org (Postfix) with ESMTP id 59364D1B4AA for ; Wed, 25 Feb 2004 15:18:26 -0400 (AST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-34.mail.demon.net with esmtp (Exim 3.35 #1) id 1Aw4Y5-00063v-0Y for pgsql-sql@postgresql.org; Wed, 25 Feb 2004 19:18:25 +0000 Received: by mainbox.archonet.com (Postfix, from userid 529) id 56A4816A1D; Wed, 25 Feb 2004 19:18:24 +0000 (GMT) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id E0D191591F for ; Wed, 25 Feb 2004 19:18:20 +0000 (GMT) From: Richard Huxton To: pgsql-sql@postgresql.org Subject: Scalar in a range (but textual not numeric) Date: Wed, 25 Feb 2004 19:18:20 +0000 User-Agent: KMail/1.5 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200402251918.20289.dev@archonet.com> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/346 X-Sequence-Number: 17036 Large table representing non-overlapping blocks: blocks(id int4, min varchar, max varchar) SELECT * FROM blocks WHERE 'ABCDE' BETWEEN min AND max; The estimator gets the wrong plan because it doesn't realise there's (at most) only one block that can match. Can't use any of the geometry related types since we've got text here not numbers. Nothing in the archives seems quite right (AFAICT). Any smart ideas? I'm happy to trade time when updating the blocks table against lookup speed. -- Richard Huxton Archonet Ltd