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