How to create index on records for last 90 days in Postgres Making now() immutable

I have a case where due to speed issues I only want to create the index on records for last 90 days.

When I try to create index like this:

create index if not exists idx_d1_section_learner_partial_date_modified
    on instruct.d1_section_learner (audit_modified_datetime)
    where (audit_modified_datetime >= '2019-07-01 00:00:00'::timestamp);

It works, but I wanted to keep this dynamic so I tried this:

create index if not exists idx_d1_section_learner_partial_date_modified
    on instruct.d1_section_learner (audit_modified_datetime)
    where (audit_modified_datetime >= now() - interval '90 days'::timestamp);

It gives error:

ERROR: functions in index predicate must be marked IMMUTABLE

I know that is happening because of current timestamp. Because it’s not constant in transaction. Is there a way I can do this avoiding that?

Or maybe can I mark current_timestamp as immutable?

Answer

Instead of an immutable now() function (which wouldn’t work!) use a pseudo-immutable function returning a timestamp constant, and base your partial index on it – as well as your queries that are supposed to use it.

Also, you don’t have to update the index every day. The index can hold a couple of outdated rows, that’s hardly relevant. You just add an exact condition to your queries additionally. Performance deteriorates slowly over time as more rows are added. It’s enough to recreate function and index from time to time. Can be every week at times with the lowest DB load.

It just so happens that I posted a complete solution for the case 6 years ago:

• Optimize performance for queries on recent rows of a large table

Updated it a bit to reflect recent developments.

Aside: now() returns timestamptz, not timestamp. LOCALTIMESTAMP would be a better fit. But don’t go there.