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

------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan 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'::timestamp without
time zone) AND (begin_time < '2005-10-04 00:00:00'::timestamp without
time zone))
Total runtime: 52335.126 ms

-Dave