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.rice@gmail.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
rightnow 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!