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

   1 -- This converts the old hot_rank functions, to columns
   2 -- Remove the old compound indexes
   3 DROP INDEX idx_post_aggregates_featured_local_newest_comment_time;
   4
   5 DROP INDEX idx_post_aggregates_featured_community_newest_comment_time;
   6
   7 DROP INDEX idx_post_aggregates_featured_local_comments;
   8
   9 DROP INDEX idx_post_aggregates_featured_community_comments;
  10
  11 DROP INDEX idx_post_aggregates_featured_local_hot;
  12
  13 DROP INDEX idx_post_aggregates_featured_community_hot;
  14
  15 DROP INDEX idx_post_aggregates_featured_local_score;
  16
  17 DROP INDEX idx_post_aggregates_featured_community_score;
  18
  19 DROP INDEX idx_post_aggregates_featured_local_published;
  20
  21 DROP INDEX idx_post_aggregates_featured_community_published;
  22
  23 DROP INDEX idx_post_aggregates_featured_local_active;
  24
  25 DROP INDEX idx_post_aggregates_featured_community_active;
  26
  27 DROP INDEX idx_comment_aggregates_hot;
  28
  29 DROP INDEX idx_community_aggregates_hot;
  30
  31 -- Add the new hot rank columns for post and comment aggregates
  32 -- Note: 1728 is the result of the hot_rank function, with a score of 1, posted now
  33 -- hot_rank = 10000*log10(1 + 3)/Power(2, 1.8)
  34 ALTER TABLE post_aggregates
  35     ADD COLUMN hot_rank integer NOT NULL DEFAULT 1728;
  36
  37 ALTER TABLE post_aggregates
  38     ADD COLUMN hot_rank_active integer NOT NULL DEFAULT 1728;
  39
  40 ALTER TABLE comment_aggregates
  41     ADD COLUMN hot_rank integer NOT NULL DEFAULT 1728;
  42
  43 ALTER TABLE community_aggregates
  44     ADD COLUMN hot_rank integer NOT NULL DEFAULT 1728;
  45
  46 -- Populate them initially
  47 -- Note: After initial population, these are updated in a periodic scheduled job,
  48 -- with only the last week being updated.
  49 UPDATE
  50     post_aggregates
  51 SET
  52     hot_rank_active = hot_rank (score::numeric, newest_comment_time_necro);
  53
  54 UPDATE
  55     post_aggregates
  56 SET
  57     hot_rank = hot_rank (score::numeric, published);
  58
  59 UPDATE
  60     comment_aggregates
  61 SET
  62     hot_rank = hot_rank (score::numeric, published);
  63
  64 UPDATE
  65     community_aggregates
  66 SET
  67     hot_rank = hot_rank (subscribers::numeric, published);
  68
  69 -- Create single column indexes
  70 CREATE INDEX idx_post_aggregates_score ON post_aggregates (score DESC);
  71
  72 CREATE INDEX idx_post_aggregates_published ON post_aggregates (published DESC);
  73
  74 CREATE INDEX idx_post_aggregates_newest_comment_time ON post_aggregates (newest_comment_time DESC);
  75
  76 CREATE INDEX idx_post_aggregates_newest_comment_time_necro ON post_aggregates (newest_comment_time_necro DESC);
  77
  78 CREATE INDEX idx_post_aggregates_featured_community ON post_aggregates (featured_community DESC);
  79
  80 CREATE INDEX idx_post_aggregates_featured_local ON post_aggregates (featured_local DESC);
  81
  82 CREATE INDEX idx_post_aggregates_hot ON post_aggregates (hot_rank DESC);
  83
  84 CREATE INDEX idx_post_aggregates_active ON post_aggregates (hot_rank_active DESC);
  85
  86 CREATE INDEX idx_comment_aggregates_hot ON comment_aggregates (hot_rank DESC);
  87
  88 CREATE INDEX idx_community_aggregates_hot ON community_aggregates (hot_rank DESC);
  89