Re: Index lookup on > and < criteria

Yeah, analyze did make a difference.  See below.

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

sipcdr=# analyze october_cdr_call;
ANALYZE

sipcdr=# explain analyze select * from october_cdr_call where begin_time >= '10/1/2005' and begin_time < '10/4/2005';
QUERY PLAN 


------------------------------------------------------------------------------------------------------------------------------------------------------
SeqScan on october_cdr_call  (cost=0.00..285695.68 rows=500922 
 
width=371) (actual time=54.510..50004.458 rows=538592 loops=1)   Filter: ((begin_time >= '2005-10-01
00:00:00'::timestampwithout 
 
time zone) AND (begin_time < '2005-10-04 00:00:00'::timestamp without 
time zone)) Total runtime: 52335.126 ms


-Dave