From: | Chris Angelico <rosuav(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Seeking performance advice: Index for "recent entries" |
Date: | 2012-05-09 03:52:37 |
Message-ID: | CAPTjJmrC8DwRwCXSZ_DXBEYgRUSPFRwmxQEWi2j+m1zqvWiWPQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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'
with a possible REINDEX or ALTER INDEX or even DROP INDEX/CREATE INDEX
periodically to "prune" the index. However, Postgres complains:
ERROR: functions in index predicate must be marked IMMUTABLE
Without the arithmetic, it works fine:
CREATE INDEX ledgernotyet on larcombe.ledger (lid,effective) where
effective>timestamptz 'now'
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?
Thanks!
Chris Angelico
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-05-09 04:52:45 | Re: Seeking performance advice: Index for "recent entries" |
Previous Message | Tomas Vondra | 2012-05-08 23:00:09 | Re: relpages sudden increase |