Re: difficulties with time based queries

"Rainer Mager" <rainer(at)vanten(dot)com> writes:
> explain analyze select * from ad_log where date(start_time) <
> date('2009-03-31') and date(start_time) >= date('2009-03-30');

> Bitmap Heap Scan on ad_log (cost=73372.57..3699152.24 rows=2488252
> width=32) (actual time=49792.862..64611.255 rows=2268490 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..72750.51
> rows=2488252 width=0) (actual time=49776.332..49776.332 rows=2268490
> loops=1)
> Index Cond: ((date(start_time) < '2009-03-31'::date) AND
> (date(start_time) >= '2009-03-30'::date))
> Total runtime: 65279.352 ms

Hmm ... it's pretty unusual to see the index fetch portion of a bitmap
scan take the bulk of the runtime. Usually that part is fast and where
the pain comes is in fetching from the heap. I wonder whether that
index has become bloated. How big are the table and the index
physically? (Look at pg_class.relpages, or if you want a really
accurate number try pg_relation_size().)

What Postgres version is this, exactly?

BTW, I think you've gone way overboard in your indexing of this table;
those indexes are certainly consuming well more space than the table
does, and a lot of them are redundant.

regards, tom lane