From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Major <major@cbio.mskcc.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help w/speeding up range queries?
Date: Tue, 31 Oct 2006 23:29:09 -0500
Message-ID: <12189.1162355349@sss.pgh.pa.us> (raw)
In-Reply-To: <4547D9CE.2040705@cbio.mskcc.org>
References: <4547D9CE.2040705@cbio.mskcc.org>
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.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, major@cbio.mskcc.org
Subject: Re: Help w/speeding up range queries?
In-Reply-To: <12189.1162355349@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox