Seeking performance advice: Index for "recent entries"

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

Responses

Browse pgsql-general by date

  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