From: Richard Huxton <dev@archonet.com>
To: pgsql-sql@postgresql.org
Subject: Re: Scalar in a range (but textual not numeric)
Date: Wed, 25 Feb 2004 20:36:40 +0000
User-Agent: KMail/1.5
References: <200402251918.20289.dev@archonet.com>
In-Reply-To: <200402251918.20289.dev@archonet.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200402252036.40747.dev@archonet.com>

On Wednesday 25 February 2004 19:18, Richard Huxton wrote:
> 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.

Well, replying to myself (just one of my many bad habits) the best I've come 
up with so far is to add another column with a trimmed string and do a direct 
comparison against that too:

SELECT * FROM blocks WHERE substring('ABCDE',1,3)=block_segment AND 'ABCDE' 
BETWEEN min AND max

This gives the planner something to work with, and on 7.4 it even renders it 
down to 'ABC' first too (nice :-)

That's not quite the same though, because it means I need to split 
ABCAA..ABDBB into ABCAA..ABCZZ and ABDAA..ABDZZ but it's close enough unless 
someone is feeling clever this evening.

-- 
  Richard Huxton
  Archonet Ltd