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

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.

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.

In this case, partitioning is only really useful when you are going to be 
forced to do seq scans. If you can get a suitably selective index, in the 
case where you are selecting a small proportion of the data, then I would 
concentrate on getting the index right, rather than the partition, and 
maybe even not do partitioning.

Matthew

-- 
 Trying to write a program that can't be written is... well, it can be an
 enormous amount of fun!                 -- Computer Science Lecturer