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

Dave Steinberg <dave-dated-1101824919(dot)46cd20(at)redterror(dot)net> writes:
> -> Seq Scan on messages (cost=0.00..21573.04 rows=436426 width=54) (actual time=5.523..6304.657 rows=462931 loops=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