From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Chris Angelico <rosuav(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Seeking performance advice: Index for "recent entries" |
Date: | 2012-05-09 04:52:45 |
Message-ID: | 9814.1336539165@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Angelico | 2012-05-09 06:00:57 | Re: Seeking performance advice: Index for "recent entries" |
Previous Message | Chris Angelico | 2012-05-09 03:52:37 | Seeking performance advice: Index for "recent entries" |