Would creating an index on the timestamp truncated to the *day* make the index more efficient for queries which are interested in events falling in a range of 7+ days?
I gave this a shot, changing the index to be on date_trunc('day', timestamp). PostgreSQL (8.2) then decided not to use the index at all. Not sure if this is a limitation of PostgreSQL or if it decided it was going to be more expensive than the alternative.
Is the query optimizer capable of using the relationship between an index on date_trunc(foo) and a query with "where foo < bar and foo > baz" ? At this point the question is to satisfy my own curiosity.
Derrick
