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

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.

> Is there a way around this? Also, how would I go about pruning the
> index, preferably in such a way that the old index can be used?

And that is exactly the reason why a partial index of this sort isn't a
win --- you'd be expending many extra cycles to keep it trimmed.

            regards, tom lane