如何在 postgres 中基於基於時間的函式創建索引？

我有一個函式呼叫hot_rank來根據他們的分數和他們的最新評論時間（基本上是一個衰減的冪函式）對文章進行排名：

create or replace function hot_rank(
 score numeric,
 published timestamp without time zone)
returns integer as $$
begin
 -- hours_diff:=EXTRACT(EPOCH FROM (timezone('utc',now()) - published))/3600
 return floor(10000*log(greatest(1,score+3)) / power(((EXTRACT(EPOCH FROM now() - published)/3600) + 2), 1.8))::integer;
end; $$
LANGUAGE plpgsql
IMMUTABLE;

然後，我使用該函式在此表上創建一個索引：

create index idx_post_aggregates_active on post_aggregates (hot_rank(score, newest_comment_time) desc, newest_comment_time desc);

隨著時間的推移，我開始注意到奇怪的結果，所以我查看了表格，當我按 排序時hot_rank(...，數值看起來不錯，但順序錯誤。

然後我reindex在那個索引上執行，一切都再次正確排序。

什麼會導致該索引在一段時間後“變壞”？是什麼導致索引排序錯誤？我是否錯誤地將實際可變的函式定義為不可變的？

我在這裡做錯了什麼，有什麼方法可以解決這個問題？

編輯：這是索引：

create index idx_post_aggregates_active on post_aggregates (hot_rank(score, newest_comment_time) desc, newest_comment_time desc);

和查詢：

select 
p.id,
p.name,
pa.newest_comment_time,
pa.score,
hot_rank(pa.score, pa.newest_comment_time)
from post p
inner join post_aggregates pa
on pa.post_id = p.id
order by hot_rank(pa.score, pa.newest_comment_time) desc, pa.newest_comment_time desc
limit 50;

您的函式不是不可變的，但它被聲明為不可變的。正如您所發現的，這種欺騙將導致索引損壞。每個條目都將以其在插入/更新時的分數出現在索引中。

解決這個問題的方法是退後一步，看看你正在嘗試做什麼。冪函式的意義何在？ 它不會改變點的順序（當然，如果它們沒有綁定到最大（1，score+3）），那麼它在您的使用中完成了什麼？轉換為整數有什麼意義？數據在表中保留多長時間？

如果你有一個隨時間變化的表的順序，你不能用索引來持久化它。相反，您必須在查詢表時計算順序。

引用自：https://dba.stackexchange.com/questions/284052