Re: Optimize date query for large child tables: GiST or GIN?

>> Regarding the leap year problem, you might consider creating a modified day
>> of year field, which always assumes that the year contains a leap day. Then
>> a given number always resolves to a given date, regardless of year. If you
>> then partition (or index) on that field, then you may get a benefit.
On Fri, 21 May 2010, Yeb Havinga wrote:
> Shouldn't it be just the other way around - assume all years are non leap
> years for the doy part field to be indexed.

The mapping doesn't matter massively, as long as all days of the year can
be mapped uniquely onto a number, and the numbers are sequential. Your
suggestion does not satisfy the first of those two requirements.

If you assume that all yeasr are leap years, then you merely skip a number
in the middle of the year, which isn't a problem when you want to check
for days between two bounds. However, if you assume non leap year, then
there is no representation for the 29th of February, so not all data
points will have a representative number to insert into the database.

Matthew

--
No, C++ isn't equal to D. 'C' is undeclared, so we assume it's an int,
with a default value of zero. Hence, C++ should really be called 1.
-- met24, commenting on the quote "C++ -- shouldn't it be called D?"