Re: Index lookup on > and < criteria

On Tue, Nov 01, 2005 at 03:21:21PM -0600, David Durham wrote:
> sipcdr=# explain analyze select * from october_cdr_call where begin_time
> >= '10/1/2005' and begin_time < '10/4/2005';
>
> QUERY PLAN
>
> --------------------------------------------------------------------------------
> --------------------------------------------------------------------------
> Index Scan using october_begin_time on october_cdr_call
> (cost=0.00..98383.82 r
> ows=24594 width=568) (actual time=0.280..79274.579 rows=538592 loops=1)
> Index Cond: ((begin_time >= '2005-10-01 00:00:00'::timestamp without
> time zon
> e) AND (begin_time < '2005-10-04 00:00:00'::timestamp without time zone))
> Total runtime: 81457.938 ms
> (3 rows)

The estimated row count (24594) is much different than the actual
row count (538592), which makes me wonder if the statistics are up
to date. Try running ANALYZE on the table and then see if the
estimate is more accurate. With a more accurate estimate the planner
might choose a sequential scan, but the other queries you posted
suggest that a sequential scan is indeed faster when you're fetching
this much data.

> >Has the table been vacuumed and analyzed?
>
> Brand new table that I haven't deleted anything from yet.

The table should still be analyzed to update the planner's statistics.
The planner uses statistics to estimate how many rows a query will
return, and that influences the choice of plan.

> >How much memory do you have and what's your effective_cache_size
> >setting?
>
> 1.5 gig RAM, effective_cache_size is the default, so 1000.

You'd probably benefit from raising effective_cache_size to reflect
the amount of memory being used for disk cache, both by PostgreSQL
and by the operating system; you might also benefit from adjusting
other settings like shared_buffers. See a tuning guide like the
following for advice:

http://www.powerpostgresql.com/PerfList

--
Michael Fuhr