Re: Help w/speeding up range queries?

John Major <major(at)cbio(dot)mskcc(dot)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.

regards, tom lane