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

Hi,

check (taken >= '1913-12-01' and taken <= '1913-12-31')
>

I don't think I want to constrain by year, for a few reasons:

1. There are a lot of years -- over 110.
2. There will be more years added (both in the future for 2010 and in the
past as I get data from other sources).

Currently I have it constrained by month and category. Each table then has
about 3 million rows (which is 216 million, but some tables have more, which
brings it to 273 million).

> /* Data before 1900 is shaky; insufficient after 2009. */
> -- I have no idea why this is here.. Aren't you forcing
>

Mostly temporary. It is also constrained by the user interface; however that
will likely change in the future. It should not be present in the database
structure itself.

> /* Between the selected days and years... */
>
> CASE
> WHEN (user_start_year || user_start_day <= user_stop_year ||
> user_stop) THEN
> m.taken BETWEEN user_start_year || user_start_day AND
> user_stop_year || user_stop
> WHEN (user_start_year || user_start_day > user_stop_year ||
> user_stop) THEN
> m.taken BETWEEN (user_start_year || user_start_day)::date AND
> ((user_stop_year || user_stop)::date + '1
> year'::interval)::date
> -- I don't think you need/want this..?
>

User selects this:

1. Years: 1950 to 1974
2. Days: Dec 22 to Mar 22

This means that the query must average data between Dec 22 1950 and Mar 22
1951 for the year of 1950. For 1951, the range is Dec 22 1951 to Mar 22
1952, and so on. If we switch the calendar (or alter the seasons) so that
winter starts Jan 1st (or ends Dec 31), then I could simplify the query. ;-)

Dave