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
Re: force the use of a particular index |
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: