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.

[snip]
> 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
[snip faster plan]

>
> Any ideas on how I can fix this. I get this problem now
> and again with other databases but a vacuum usually fixes
> it.

The planner is not very good at estimating selectivity of
single unequalities. If you can specify a range in the
where clause, you might possibly have better luck.
...WHERE msgtime < cast(now() - interval '6 months'
as timestamp(0) without time zone
AND msgtime >= '2000-01-01'

Also, you might want to try to increase the STATISTICS target
of msgtime.

Sometimes an ORDER BY clause can help the planner on choosing indexscan,
although in this case the difference in estimated cost is so high that
I doubt it.

gnari