如何在 Postgres 中对过去 90 天的记录创建索引 使 now() 不可变

Question

我有一个情况，由于速度问题，我只想在过去 90 天的记录上创建索引。

当我尝试创建这样的索引时：

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);

它有效，但我想保持这种动态，所以我尝试了这个：

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);

它给出错误：

错误：索引谓词中的函数必须标记为不可变

我知道这是由于当前的时间戳而发生的。因为它在交易中不是恒定的。有没有办法避免这种情况？

或者我可以将current_timestamp标记为不可变吗？

Answer 1

而不是不可变的now()函数(这行不通！(使用一个返回timestamp常量的伪不可变函数，并将你的部分索引基于它 - 以及你应该使用它的查询。

此外，您不必每天更新索引。索引可以容纳几个过时的行，这几乎无关紧要。您只需另外向查询添加确切的条件即可。随着添加的行数增加，性能会随着时间的推移而缓慢下降。不时重新创建函数和索引就足够了。可以在数据库负载最低的每周进行。

碰巧我在6 年前发布了该案例的完整解决方案：

• 优化对大型表的最近行的查询性能

对其进行了一些更新以反映最近的发展。

旁白：now()返回timestamptz，而不是timestamp。LOCALTIMESTAMP会更合适。但不要去那里。