Re: force the use of a particular index
От | Scott Cain |
---|---|
Тема | Re: force the use of a particular index |
Дата | |
Msg-id | 1057929459.5766.7.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | Re: force the use of a particular index (Rod Taylor <rbt@rbt.ca>) |
Список | pgsql-performance |
The problem (at least as it appears to me) is not that it is performing a table scan instead of an index scan, it is that it is using the wrong index. Here is the output from EXPLAIN ANALYZE: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Unique (cost=494008.47..494037.59 rows=166 width=54) (actual time=114660.37..114660.38 rows=1 loops=1) -> Sort (cost=494008.47..494012.63 rows=1664 width=54) (actual time=114660.37..114660.37 rows=1 loops=1) Sort Key: f.name, fl.fmin, fl.fmax, fl.strand, f.type_id, f.feature_id -> Nested Loop (cost=0.00..493919.44 rows=1664 width=54) (actual time=2596.13..114632.90 rows=1 loops=1) -> Index Scan using feature_pkey on feature f (cost=0.00..134601.43 rows=52231 width=40) (actual time=105.74..56048.87rows=13825 loops=1) Filter: (type_id = 219) -> Index Scan using featureloc_idx1 on featureloc fl (cost=0.00..6.87 rows=1 width=14) (actual time=4.23..4.23rows=0 loops=13825) Index Cond: ("outer".feature_id = fl.feature_id) Filter: ((srcfeature_id = 6) AND (fmin <= 2585581) AND (fmax >= 2565581)) Total runtime: 114660.91 msec This is the same regardless of enable_seqscan's setting. The index that it is using on featureloc (featureloc_idx1) is on the foreign key feature_id. It should instead be using another index, featureloc_idx3, which is built on (srcfeature_id, fmin, fmax). I should also mention that I've done a VACUUM FULL ANALYZE on this database, and I've been using it for a while, and this is the primary type of query I perform on the database. Thanks, Scott On Fri, 2003-07-11 at 06:51, Rod Taylor wrote: > On Thu, 2003-07-10 at 15:18, Scott Cain wrote: > > 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: > > Please send the EXPLAIN ANALYZE results for that query with and without > sequential scans enabled. > > set enable_seqscan = true; > EXPLAIN ANALYZE <query>; > > set enable_seqscan = false; > EXPLAIN ANALYZE <query>; -- ------------------------------------------------------------------------ Scott Cain, Ph. D. cain@cshl.org GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory
В списке pgsql-performance по дате отправления: