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 91397D1B97B for ; Wed, 25 Feb 2004 22:37: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 56425-09 for ; Wed, 25 Feb 2004 18:37:33 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 5FBAED1B967 for ; Wed, 25 Feb 2004 18:37:30 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i1PMbUSN007220; Wed, 25 Feb 2004 17:37:30 -0500 (EST) To: Richard Huxton Cc: pgsql-sql@postgresql.org Subject: Re: Scalar in a range (but textual not numeric) In-reply-to: <7179.1077748348@sss.pgh.pa.us> References: <200402251918.20289.dev@archonet.com> <6803.1077744728@sss.pgh.pa.us> <200402252208.55502.dev@archonet.com> <7179.1077748348@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Wed, 25 Feb 2004 17:32:28 -0500" Date: Wed, 25 Feb 2004 17:37:30 -0500 Message-ID: <7219.1077748650@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/353 X-Sequence-Number: 17043 I wrote: > try writing > WHERE 'ABCDE' >= pr_min AND 'ABCDE' <= pr_max > AND pr_min < (SELECT pr_min FROM table > WHERE pr_min > 'ABCDE' > ORDER BY pr_min LIMIT 1) > The idea here is to add an upper bound on pr_min to the index scan > conditions, so that the scan can stop short of the end of the index. Argh, got that backwards. What you are missing is a *lower* bound on pr_min, and the index scan will therefore run from the start of the index up to pr_min = 'ABCDE'. So reverse the sense of the added test: AND pr_min >= (SELECT pr_min FROM table WHERE pr_min <= 'ABCDE' ORDER BY pr_min DESC LIMIT 1) regards, tom lane