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