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

Best Answer

Your function is not immutable, yet it is declared to be immutable. This deception will lead to corrupted to indexes, as you have discovered. Each entry will be in the index with the score it had at the time it was inserted/updated.

The way to fix this is to take a step back and look at what you are trying to do. What is the point of the power function at all? It won't change the ordering of the points (well it will of course if they are not tied in greatest(1,score+3)), so what does it accomplish in your usage? What is the point of converting to integer? How long is data retained in the table?