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"  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: MD33
Дата:
Сообщение: Re: COPY column order
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Seeking performance advice: Index for "recent entries"