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

   1 -- Need to add immutable to the hot_rank function in order to index by it
   2
   3 -- Rank = ScaleFactor * sign(Score) * log(1 + abs(Score)) / (Time + 2)^Gravity
   4 create or replace function hot_rank(
   5   score numeric,
   6   published timestamp without time zone)
   7 returns integer as $$
   8 begin
   9   -- hours_diff:=EXTRACT(EPOCH FROM (timezone('utc',now()) - published))/3600
  10   return floor(10000*log(greatest(1,score+3)) / power(((EXTRACT(EPOCH FROM (timezone('utc',now()) - published))/3600) + 2), 1.8))::integer;
  11 end; $$
  12 LANGUAGE plpgsql
  13 IMMUTABLE;
  14
  15 -- Post
  16 create index idx_post_published on post (published desc);
  17 create index idx_post_stickied on post (stickied desc);
  18
  19 -- Post_aggregates
  20 create index idx_post_aggregates_hot on post_aggregates (hot_rank(score, published) desc, published desc);
  21 create index idx_post_aggregates_active on post_aggregates (hot_rank(score, newest_comment_time) desc, newest_comment_time desc);
  22 create index idx_post_aggregates_score on post_aggregates (score desc);
  23
  24 -- Comment
  25 create index idx_comment_published on comment (published desc);
  26
  27 -- Comment_aggregates
  28 create index idx_comment_aggregates_hot on comment_aggregates (hot_rank(score, published) desc, published desc);
  29 create index idx_comment_aggregates_score on comment_aggregates (score desc);
  30
  31 -- User
  32 create index idx_user_published on user_ (published desc);
  33
  34 -- User_aggregates
  35 create index idx_user_aggregates_comment_score on user_aggregates (comment_score desc);
  36
  37 -- Community
  38 create index idx_community_published on community (published desc);
  39
  40 -- Community_aggregates
  41 create index idx_community_aggregates_hot on community_aggregates (hot_rank(subscribers, published) desc, published desc);
  42 create index idx_community_aggregates_subscribers on community_aggregates (subscribers desc);
  43
  44
  45