On Oct 31, 2006, at 8:29 PM, Tom Lane wrote:
John Major <[EMAIL PROTECTED]> writes:
My problem is, I often need to execute searches of tables like these
which find "All features within a range".
Ie: select FeatureID from SIMPLE_TABLE where
FeatureChromosomeName like
'chrX' and StartPosition > 1000500 and EndPosition < 2000000;
A standard btree index is just going to suck for these types of
queries;
you need something that's actually designed for spatial range queries.
You might look at the contrib/seg module --- if you can store your
ranges as "seg" datatype then the seg overlap operator expresses what
you need to do, and searches on an overlap operator can be handled
well
by a GIST index.
Also, there's the PostGIS stuff, though it might be overkill for what
you want.
Another possibility (think Tom has suggested in the past) is to
define Start and End as a box, and then use the geometric functions
built into plain PostgreSQL (though perhaps that's what he meant by
"PostGIS stuff").
--
Jim Nasby [EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
