Seeking performance advice: Index for "recent entries"
| От | Chris Angelico |
|---|---|
| Тема | Seeking performance advice: Index for "recent entries" |
| Дата | |
| Msg-id | CAPTjJmrC8DwRwCXSZ_DXBEYgRUSPFRwmxQEWi2j+m1zqvWiWPQ@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Seeking performance advice: Index for "recent entries"
|
| Список | 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
В списке pgsql-general по дате отправления: