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