migrations/2023-06-07-105918_add_hot_rank_columns/up.sql

   1 -- This converts the old hot_rank functions, to columns
   2
   3 -- Remove the old compound indexes
   4 DROP INDEX idx_post_aggregates_featured_local_newest_comment_time;
   5 DROP INDEX idx_post_aggregates_featured_community_newest_comment_time;
   6 DROP INDEX idx_post_aggregates_featured_local_comments;
   7 DROP INDEX idx_post_aggregates_featured_community_comments;
   8 DROP INDEX idx_post_aggregates_featured_local_hot;
   9 DROP INDEX idx_post_aggregates_featured_community_hot;
  10 DROP INDEX idx_post_aggregates_featured_local_score;
  11 DROP INDEX idx_post_aggregates_featured_community_score;
  12 DROP INDEX idx_post_aggregates_featured_local_published;
  13 DROP INDEX idx_post_aggregates_featured_community_published;
  14 DROP INDEX idx_post_aggregates_featured_local_active;
  15 DROP INDEX idx_post_aggregates_featured_community_active;
  16
  17 DROP INDEX idx_comment_aggregates_hot;
  18
  19 DROP INDEX idx_community_aggregates_hot;
  20
  21 -- Add the new hot rank columns for post and comment aggregates
  22 -- Note: 1728 is the result of the hot_rank function, with a score of 1, posted now
  23 -- hot_rank = 10000*log10(1 + 3)/Power(2, 1.8)
  24 alter table post_aggregates add column hot_rank integer not null default 1728;
  25 alter table post_aggregates add column hot_rank_active integer not null default 1728;
  26
  27 alter table comment_aggregates add column hot_rank integer not null default 1728;
  28
  29 alter table community_aggregates add column hot_rank integer not null default 1728;
  30
  31 -- Populate them initially
  32 -- Note: After initial population, these are updated in a periodic scheduled job,
  33 -- with only the last week being updated.
  34 update post_aggregates set hot_rank_active = hot_rank(score::numeric, newest_comment_time_necro);
  35 update post_aggregates set hot_rank = hot_rank(score::numeric, published);
  36 update comment_aggregates set hot_rank = hot_rank(score::numeric, published);
  37 update community_aggregates set hot_rank = hot_rank(subscribers::numeric, published);
  38
  39 -- Create single column indexes
  40 create index idx_post_aggregates_score on post_aggregates (score desc);
  41 create index idx_post_aggregates_published on post_aggregates (published desc);
  42 create index idx_post_aggregates_newest_comment_time on post_aggregates (newest_comment_time desc);
  43 create index idx_post_aggregates_newest_comment_time_necro on post_aggregates (newest_comment_time_necro desc);
  44 create index idx_post_aggregates_featured_community on post_aggregates (featured_community desc);
  45 create index idx_post_aggregates_featured_local on post_aggregates (featured_local desc);
  46 create index idx_post_aggregates_hot on post_aggregates (hot_rank desc);
  47 create index idx_post_aggregates_active on post_aggregates (hot_rank_active desc);
  48
  49 create index idx_comment_aggregates_hot on comment_aggregates (hot_rank desc);
  50
  51 create index idx_community_aggregates_hot on community_aggregates (hot_rank desc);