Re: Scalar in a range (but textual not numeric)

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