Index not being used unless enable_seqscan=false

From: Shane <shane-pgsql(at)cm(dot)nu>
To: pgsql-general(at)postgresql(dot)org
Subject: Index not being used unless enable_seqscan=false
Date: 2005-08-10 19:01:28
Message-ID: 20050810190128.GA2659@cm.nu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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=28907 loops=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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mark Harrison 2005-08-10 19:15:10 accessing postgresql via odbc?
Previous Message Martijn van Oosterhout 2005-08-10 18:48:47 Re: 5 new entries for FAQ