On Wed, May 9, 2012 at 2:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Chris Angelico <rosuav@gmail.com> writes:
>> I have a table with a timestamptz column for the "effective date/time"
>> of the row, and need to have some queries that look only for those
>> entries for which that is in the future or VERY recently - which will
>> be a small minority of rows. I'm looking at something like:
>
>> CREATE INDEX on tablename (effective) where effective>timestamptz
>> 'now'-interval '21 days'
>
> I think this falls under the rubric of "premature optimization is the
> root of all evil". Just use a plain index on the timestamptz column
> and be happy. Searches that only look at the extremal values of a
> column work perfectly well with a full index, because they only need to
> examine a small range of the index.
The index is actually on two columns, an account ID followed by the
effective date - I need to look up whether any particular account has
recent entries. Does that make any difference?
Thanks for the advice. I don't have enough data yet to be able to
measure these things, and it's good to solve problems before they
become critical!
ChrisA