migrations/2021-01-05-200932_add_hot_rank_indexes/down.sql

   1 -- Rank = ScaleFactor * sign(Score) * log(1 + abs(Score)) / (Time + 2)^Gravity
   2 create or replace function hot_rank(
   3   score numeric,
   4   published timestamp without time zone)
   5 returns integer as $$
   6 begin
   7   -- hours_diff:=EXTRACT(EPOCH FROM (timezone('utc',now()) - published))/3600
   8   return floor(10000*log(greatest(1,score+3)) / power(((EXTRACT(EPOCH FROM (timezone('utc',now()) - published))/3600) + 2), 1.8))::integer;
   9 end; $$
  10 LANGUAGE plpgsql;
  11
  12 drop index
  13   idx_post_aggregates_hot,
  14   idx_post_aggregates_stickied_hot,
  15   idx_post_aggregates_active,
  16   idx_post_aggregates_stickied_active,
  17   idx_post_aggregates_score,
  18   idx_post_aggregates_stickied_score,
  19   idx_post_aggregates_published,
  20   idx_post_aggregates_stickied_published,
  21   idx_comment_published,
  22   idx_comment_aggregates_hot,
  23   idx_comment_aggregates_score,
  24   idx_user_published,
  25   idx_user_aggregates_comment_score,
  26   idx_community_published,
  27   idx_community_aggregates_hot,
  28   idx_community_aggregates_subscribers;