Re: query by partial timestamp

On Jan 8, 2013 6:15 PM, "Kirk Wythers" <wythe001(at)umn(dot)edu> wrote:
>
>
> On Jan 8, 2013, at 6:48 PM, Tom Lane <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.

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.