From: Marcin Mank <marcin.mank@gmail.com>
To: John Major <major@cbio.mskcc.org>
To: pgsql-performance@postgresql.org
Subject: Re: Help w/speeding up range queries?
Date: Thu, 2 Nov 2006 11:54:57 +0100
Message-ID: <05e401c6fe6d$56d13780$0c67a8c0@maniek> (raw)
References: <4547D9CE.2040705@cbio.mskcc.org>
> Ie: select FeatureID from SIMPLE_TABLE where FeatureChromosomeName like
> 'chrX' and StartPosition > 1000500 and EndPosition < 2000000;
How about ( this assumes that StartPosition <= EndPosition ):
select FeatureID
from SIMPLE_TABLE
where FeatureChromosomeName llike 'chrX'
and StartPosition > 1000500
and StartPosition < 2000000
and EndPosition > 1000500
and EndPosition < 2000000;
This at least should help the planner with estimating number of rows.
Also think twice when You assume that a query with ILIKE will use an index.
Read about varchar_pattern_ops.
Make an index on (FeatureChromosomeName,StartPosition) , and all should be
fine.
Greetings
Marcin
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: marcin.mank@gmail.com, major@cbio.mskcc.org
Subject: Re: Help w/speeding up range queries?
In-Reply-To: <05e401c6fe6d$56d13780$0c67a8c0@maniek>
* 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