Re: PG7.4.5: query not using index on date column

Dave Steinberg <dave-dated-1101824919.46cd20@redterror.net> writes:
>                ->  Seq Scan on messages  (cost=0.00..21573.04 rows=436426 width=54) (actual time=5.523..6304.657
rows=462931loops=1)
 
>                      Filter: ((received_date >= '2004-11-01'::date) AND (received_date <= '2004-11-30'::date))

How many rows in the table altogether?  A rough guess is a few million
based on the estimated seqscan cost.  That would mean that this query
is retrieving about 10% of the table, which is a large enough fraction
that the planner will probably think a seqscan is best.  It may be right.
If you do "set enable_seqscan = off", how does the EXPLAIN ANALYZE
output change?

If it's not right, you may want to try to adjust random_page_cost and/or
effective_cache_size so that the planner's estimated costs are more in
line with reality.  Beware of making such adjustments on the basis of
only one test case, though.
        regards, tom lane