Index not being used unless enable_seqscan=false
От | Shane |
---|---|
Тема | Index not being used unless enable_seqscan=false |
Дата | |
Msg-id | 20050810190128.GA2659@cm.nu обсуждение исходный текст |
Ответы |
Re: Index not being used unless enable_seqscan=false
Re: Index not being used unless enable_seqscan=false Re: Index not being used unless enable_seqscan=false |
Список | pgsql-general |
Hello all, I am working with a simple table and query abut cannot seem to get it to use the index I have created. However, if I set enable_seqscan=false, the index is used and the query is much faster. I have tried a vacuum analyze but to no avail. Table layout: Table "public.seen" Column | Type | Modifiers ----------+--------------------------------+----------- group_id | integer | not null msgid | text | not null msgtime | timestamp(0) without time zone | not null Indexes: "seen_group_id_key" unique, btree (group_id, msgid) "seen_msgtime" btree (msgtime) Foreign-key constraints: "$1" FOREIGN KEY (group_id) REFERENCES groups(id) ON UPDATE CASCADE ON DELETE CASCADE explain analyze with enable_seqscan=true explain analyze select msgid from seen where msgtime < cast(now() - interval '6 months' as timestamp(0) without time zone); QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Seq Scan on seen (cost=0.00..107879.45 rows=1081044 width=46) (actual time=7597.387..27000.777 rows=28907 loops=1) Filter: (msgtime < ((now() - '6 mons'::interval))::timestamp(0) without time zone) Total runtime: 27096.337 ms (3 rows) Same query with enable_seqscan=false QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Index Scan using seen_msgtime on seen (cost=0.00..3818325.78 rows=1081044 width=46) (actual time=0.140..156.222 rows=28907loops=1) Index Cond: (msgtime < ((now() - '6 mons'::interval))::timestamp(0) without time zone) Total runtime: 248.737 ms (3 rows) Any ideas on how I can fix this. I get this problem now and again with other databases but a vacuum usually fixes it. Thanks, Shane
В списке pgsql-general по дате отправления: