On Wed, 2005-08-10 at 12:01 -0700, Shane wrote: > 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.
Right off the bat (if I am interpreting the results of your explain analyze correctly) it looks like the planner is basing its decision to seqscan as it thinks that it needs to filter over 1 million rows (versus the 29,000 rows that actually are pulled). Perhaps increasing stats on msgtime and then analyzing the table may help. Depending on your hardware, decreasing random_page_cost in your postgresql.conf just a touch may help too. Sven ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
