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

Thom Brown <thombrown(at)gmail(dot)com> writes:
> On 20 May 2010 17:36, David Jarvis <thangalin(at)gmail(dot)com> wrote:
> Okay, get your app to convert the month-date to a day of year, so we
> have year_start, year_end, day_of_year_start, day_of_year_end

> and your where clause would something like this:

> WHERE extract(YEAR from m.taken) BETWEEN year1 and year2
> AND (
> extract(DOY from m.taken) BETWEEN day_of_year_start AND day_of_year_end
> OR (
> extract(DOY from m.taken) >= day_of_year_start OR extract(DOY from
> m.taken) <= day_of_year_end
> )
> )

extract(DOY) seems a bit problematic here, because its day numbering is
going to be different between leap years and non-leap years, and David's
problem statement doesn't allow for off-by-one errors. You could
certainly invent your own function that worked similarly but always
translated a given month/day to the same number.

The other thing that's messy here is the wraparound requirement.
Rather than trying an OR like the above (which I think doesn't quite
work anyway --- won't it select everything?), it would be better if
you can have the app distinguish wraparound from non-wraparound cases
and issue different queries in the two cases. In the non-wrap case
(start_day < end_day) it's pretty easy, just
my_doy(m.taken) BETWEEN start_val AND end_val
The easy way to handle the wrap case is
my_doy(m.taken) <= start_val OR my_doy(m.taken) >= end_val
although I can't help feeling there should be a smarter way to do
this where you can use an AND range check on some modified expression
derived from the date.

regards, tom lane