Re: query by partial timestamp

[Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx>]

On 01/08/2013 06:15 PM, Kirk Wythers wrote:

On Jan 8, 2013, at 6:48 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

The OP didn't
suggest how many years his data covers, but it's quite possible that
pulling a full year's worth of data will read enough of the table that
there's no point in worrying about whether an index could be used
anyway.

There are only a few years worth of data, 2008 - 2012. However, the data consists of 15 min measurements and when renormalized (un-pivoted) is several hundred million records. It is conceivable that someone will want to query by month, or even hour of the day. 

As another poster mentioned, you may want to consider partitioning the table not only for performance but also for eventual archiving/purging of the data.

As long as we are looking at a variety of alternatives, appropriate construction of partial indexes and the query *might* be of value but at Tom and I mentioned previously, indexes become more of a hindrance than a help once you start writing queries that access too much of the table so the planner won't use them in those cases.

Cheers,
Steve