Re: query by partial timestamp

On 01/08/2013 06:15 PM, Kirk Wythers wrote:
>
> On Jan 8, 2013, at 6:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> 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