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
