# Postgresql – How to create an index based on a time-based function in postgres?

functionspostgresqltimestamp

I have a function called hot_rank to rank posts, based on their score, and their newest_comment_time (basically a decaying power function):

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;


I then create an index on this table, using that function:

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

Over time, I began to notice strange results, so I looked at the table, and when I ordered by the hot_rank(..., the numeric values seemed good, but the order was wrong.

I then ran reindex on that index, and everything sorted correctly again.

What would cause this index to "go bad" after a while? What's causing the index to sort wrongly? Am I incorrectly defining an actually mutable function as immutable?

What am I doing wrong here, and what's a way to fix this?

edit: here's the index:

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

And the query:

select
p.id,
p.name,
pa.score,