To: "Rainer Mager" <rainer@vanten.com>
cc: pgsql-performance@postgresql.org
Subject: Re: difficulties with time based queries 
In-reply-to: <003001c9b645$e8a7cec0$b9f76c40$@com> 
References: <003001c9b645$e8a7cec0$b9f76c40$@com>
Comments: In-reply-to "Rainer Mager" <rainer@vanten.com>
	message dated "Mon, 06 Apr 2009 08:26:11 +0900"
Date: Sun, 05 Apr 2009 20:12:39 -0400
Message-ID: <5053.1238976759@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Rainer Mager" <rainer@vanten.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