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