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?" -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
