To: Dave Steinberg <dave-dated-1101824919.46cd20@redterror.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: PG7.4.5: query not using index on date column 
In-reply-to: <20041123092816.51d7aa5e.dave@redterror.net> 
References: <20041123092816.51d7aa5e.dave@redterror.net>
Comments: In-reply-to Dave Steinberg
	<dave-dated-1101824919.46cd20@redterror.net>
	message dated "Tue, 23 Nov 2004 09:28:16 -0500"
Date: Thu, 25 Nov 2004 13:44:51 -0500
Message-ID: <1247.1101408291@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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