Re: Index not being used unless enable_seqscan=false

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=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.

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