From: Scott Cain <cain@cshl.org>
To: pgsql-performance@postgresql.org
Subject: force the use of a particular index
Date: 10 Jul 2003 11:18:01 -0400
Message-ID: <1057850280.1451.22.camel@localhost.localdomain> (raw)
Hello,
I am wondering if there is a way to force the use of a particular index
when doing a query. I have two tables that are pretty big (each >3
million rows), and when I do a join between them the performance is
generally quite poor as it does not use the indexes that I think it
should use. Here is an example query:
SELECT DISTINCT f.name,fl.fmin,fl.fmax,fl.strand,f.type_id,f.feature_id
FROM feature f, featureloc fl
WHERE
f.feature_id = fl.feature_id and
fl.srcfeature_id = 6 and fl.fmin <= 2585581 and fl.fmax >= 2565581 and
f.type_id = 219
Now, I know that if the query planner will use an index on featureloc on
(srcfeature_id, fmin, fmax) that will reduce the amount of data from the
featureloc table from over 3 million to at most a few thousand, and it
will go quite quickly (if I drop other indexes on this table, it does
use that index and completes in about 1/1000th of the time). After
that, the join with the feature table should go quite quickly as well
using the primary key on feature.
So, the question is, is there a way I can force the query planner to use
the index I want it to use? I have experimented with using INNER JOIN
and changing the order of the tables in the join clause, but nothing
seems to work. Any suggestions?
Thanks much,
Scott
--
------------------------------------------------------------------------
Scott Cain, Ph. D. cain@cshl.org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: cain@cshl.org
Subject: Re: force the use of a particular index
In-Reply-To: <1057850280.1451.22.camel@localhost.localdomain>
References: <1057850280.1451.22.camel@localhost.localdomain>
* 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