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

Matthew Wakeling wrote:
> On Fri, 21 May 2010, Yeb Havinga wrote:
>> For time based data I would for sure go for year based indexing.
>
> On the contrary, most of the queries seem to be over many years, but
> rather restricting on the time of year. Therefore, partitioning by
> month or some other per-year method would seem sensible.
The fact is that at the time I wrote my mail, I had not read a specifion
of distribution of parameters (or I missed it). That's why the sentence
of my mail before the one you quoted said: "the partitioning is only
useful for speed, if it matches how your queries select data.". In most
of the databases I've worked with, the recent data was queried most
(accounting, medical) but I can see that for climate analysis this might
be different.
> 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.
Shouldn't it be just the other way around - assume all years are non
leap years for the doy part field to be indexed.

regards,
Yeb Havinga