Message-ID: <4547E2D0.3070100@hypermediasystems.com>
Date: Tue, 31 Oct 2006 15:57:04 -0800
From: Weslee Bilodeau <weslee.bilodeau@hypermediasystems.com>
User-Agent: Thunderbird 1.5.0.7 (X11/20060913)
MIME-Version: 1.0
To: John Major <major@cbio.mskcc.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help w/speeding up range queries?
References: <4547D9CE.2040705@cbio.mskcc.org>
In-Reply-To: <4547D9CE.2040705@cbio.mskcc.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

John Major wrote:
> Hello-
> 
> #I am a biologist, and work with large datasets (tables with millions of
> rows are common).
> #These datasets often can be simplified as features with a name, and a
> start and end position (ie:  a range along a number line.  GeneX is on
> some chromosome from position 10->40)
> 
> I store  these features in tables that generally have the form:
> 
> SIMPLE_TABLE:
> FeatureID(PrimaryKey) -- FeatureName(varchar) --
> FeatureChromosomeName(varchar) -- StartPosition(int) -- EndPosition(int)
> 
> 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;
> 
> This kind of query is VERY slow, and I've tried tinkering with indexes
> to speed it up, but with little success.
> Indexes on Chromosome help a little, but it I can't think of a way to
> avoid full table scans for each of the position range queries.
> 
> Any advice on how I might be able to improve this situation would be
> very helpful.

Basic question - What version, and what indexes do you have?

Have an EXPLAIN?

Something like -

CREATE INDEX index_name ON SIMPLE_TABLE ( FeatureChromosomeName
varchar_pattern_ops, StartPosition, EndPosition );

The varchar_pattern_ops being the "key" so LIKE can use an index.
Provided of course its LIKE 'something%' and not LIKE '%something'


> 
> Thanks!
> John
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
> 
>                http://www.postgresql.org/about/donate
> 


Weslee