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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
