Re: difficulties with time based queries

So, I defragged my disk and reran my original query and it got a little
better, but still far higher than I'd like. I then rebuilt (dropped and
recreated) the ad_log_date_all index and reran the query and it is quite a
bit better:

# explain analyze select * from ad_log where date(start_time) <
date('2009-03-31') and date(start_time) >= date('2009-03-30');

QUERY PLAN

----------------------------------------------------------------------------
------------------------------------------------------------------

Bitmap Heap Scan on ad_log (cost=64770.21..3745596.62 rows=2519276
width=32) (actual time=1166.479..13862.107 rows=2275167 loops=1)

Recheck Cond: ((date(start_time) < '2009-03-31'::date) AND
(date(start_time) >= '2009-03-30'::date))

-> Bitmap Index Scan on ad_log_date_all (cost=0.00..64140.39
rows=2519276 width=0) (actual time=1143.582..1143.582 rows=2275167 loops=1)

Index Cond: ((date(start_time) < '2009-03-31'::date) AND
(date(start_time) >= '2009-03-30'::date))

Total runtime: 14547.885 ms

During the query the disk throughput peaked at 30MB/s and was mostly at
around 20MB/s, much better.

So, a few questions:

What can I do to prevent the index from getting bloated, or in whatever
state it was in?

What else can I do to further improve queries on this table? Someone
suggested posting details of my conf file. Which settings are most likely to
be useful for this?

Any other suggestions?

Thanks,

--Rainer