Re: Help w/speeding up range queries?

On Oct 31, 2006, at 8:29 PM, Tom Lane wrote:
> John Major <major@cbio.mskcc.org> 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                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)