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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings