On Jan 8, 2013 6:15 PM, "Kirk Wythers" <wythe001@umn.edu> wrote:
>
>
> On Jan 8, 2013, at 6:48 PM, Tom Lane <tgl@sss.pgh.pa.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.
If that's the case, you may want to look at creating a date dimension and possibly a time dimension for your data analysis (there's a good one to start with on the PostgreSQL wiki). I would highly recommend that you take a look at some dimensional modeling concepts (Kimball is a good place to start).
Also, you may want to look at partitioning the data if it's several hundred million rows.