force the use of a particular index

От: Scott Cain
Тема: force the use of a particular index
Дата: ,
Msg-id: 1057850280.1451.22.camel@localhost.localdomain
(см: обсуждение, исходный текст)
Ответы: Re: force the use of a particular index  (Rod Taylor)
Re: force the use of a particular index  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

force the use of a particular index  (Scott Cain, )
 Re: force the use of a particular index  (Rod Taylor, )
  Re: force the use of a particular index  (Scott Cain, )
   Re: force the use of a particular index  (Rod Taylor, )
 Re: force the use of a particular index  (Rod Taylor, )
  Re: force the use of a particular index  (Scott Cain, )
 Re: force the use of a particular index  (Scott Cain, )
  Re: force the use of a particular index  (Rod Taylor, )
   Re: force the use of a particular index  (Scott Cain, )
 Re: force the use of a particular index  (Tom Lane, )
  Re: force the use of a particular index  (Scott Cain, )
   Re: force the use of a particular index  (Tom Lane, )

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.                                         
GMOD Coordinator (http://www.gmod.org/)                     216-392-3087
Cold Spring Harbor Laboratory



В списке pgsql-performance по дате сообщения:

От: Scott Cain
Дата:
Сообщение: force the use of a particular index
От: "Shridhar Daithankar"
Дата:
Сообщение: Postgresql General Bits issue