Re: Seeking performance advice: Index for "recent entries"

On Wed, May 9, 2012 at 2:52 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Chris Angelico <rosuav(at)gmail(dot)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