Re: Speeding up index scans by truncating timestamp?

On Feb 15, 2011, at 10:00, Derrick Rice wrote:

>>
>> 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.

Providing the table definition, queries, and EXPLAIN and EXPLAIN ANALYZE output would help people provide more specific feedback.

Michael Glaesemann
grzm seespotcode net