Re: Speeding up index scans by truncating timestamp?

On 15 Feb 2011, at 16:20, Vick Khera wrote:

> On Tue, Feb 15, 2011 at 10:00 AM, Derrick Rice <derrick(dot)rice(at)gmail(dot)com> wrote:
>> 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.

> I also don't think that the storage space will be any less. A
> timestamp is always stored in the same amount of space. All you're
> doing is zeroing out the higher resolution bits of time.

I suppose it would help to cast the column to date, both in the index creation and in the queries. I don't have time right now to verify that the storage space of a date is actually less than a timestamp, but I expect it would be (although, dates in PG have an awful lot of range!).

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4d5adbbf11734598963003!