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

Hi,

certainly understand that you wouldn't want to partition by year.  It
>

Definitely not.


> does strike me that perhaps you could partition by day ranges, but you'd
>

I don't think that will work; users can choose any day range, with the most
common as Jan 1 - Dec 31, followed by seasonal ranges, followed by arbitrary
ranges.


> some of this data..  If users are going to typically use 1900-2009 for
> years, then could the information about all of those years be aggregated
> apriori to make those queries faster?
>

I'm not sure what you mean. I could create a separate table that lumps the
aggregated averages per year per station per category, but that will only
help in the one case. There are five different reporting pages (Basic
through Guru). On three of those pages the user must select arbitrary day
ranges. On one of those pages, the user can select a season, which then maps
to, for all intents and purposes, an arbitrary day range.

Only the most basic page do not offer the user a day range selection.


> Do not get hung up on having to have a separate table for every unique
> value in the column- you don't need that.  constraint_exclusion will
>

That's good advice. I have repartitioned the data into seven tables: one per
category.


> I agee with Matthew Wakeling in a different post: its probably wise to
> I would agree with this too- get it working first, then look at
> partitioning.  Even more so- work on a smaller data set to begin with
>

The query speed has now much improved thanks to everybody's advice.