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

   1 -- Remove the new columns
   2 ALTER TABLE post_aggregates
   3     DROP COLUMN hot_rank;
   4
   5 ALTER TABLE post_aggregates
   6     DROP COLUMN hot_rank_active;
   7
   8 ALTER TABLE comment_aggregates
   9     DROP COLUMN hot_rank;
  10
  11 ALTER TABLE community_aggregates
  12     DROP COLUMN hot_rank;
  13
  14 -- Drop some new indexes
  15 DROP INDEX idx_post_aggregates_score;
  16
  17 DROP INDEX idx_post_aggregates_published;
  18
  19 DROP INDEX idx_post_aggregates_newest_comment_time;
  20
  21 DROP INDEX idx_post_aggregates_newest_comment_time_necro;
  22
  23 DROP INDEX idx_post_aggregates_featured_community;
  24
  25 DROP INDEX idx_post_aggregates_featured_local;
  26
  27 -- Recreate the old indexes
  28 CREATE INDEX idx_post_aggregates_featured_local_newest_comment_time ON public.post_aggregates USING btree (featured_local DESC, newest_comment_time DESC);
  29
  30 CREATE INDEX idx_post_aggregates_featured_community_newest_comment_time ON public.post_aggregates USING btree (featured_community DESC, newest_comment_time DESC);
  31
  32 CREATE INDEX idx_post_aggregates_featured_local_comments ON public.post_aggregates USING btree (featured_local DESC, comments DESC);
  33
  34 CREATE INDEX idx_post_aggregates_featured_community_comments ON public.post_aggregates USING btree (featured_community DESC, comments DESC);
  35
  36 CREATE INDEX idx_post_aggregates_featured_local_hot ON public.post_aggregates USING btree (featured_local DESC, hot_rank ((score)::numeric, published) DESC, published DESC);
  37
  38 CREATE INDEX idx_post_aggregates_featured_community_hot ON public.post_aggregates USING btree (featured_community DESC, hot_rank ((score)::numeric, published) DESC, published DESC);
  39
  40 CREATE INDEX idx_post_aggregates_featured_local_score ON public.post_aggregates USING btree (featured_local DESC, score DESC);
  41
  42 CREATE INDEX idx_post_aggregates_featured_community_score ON public.post_aggregates USING btree (featured_community DESC, score DESC);
  43
  44 CREATE INDEX idx_post_aggregates_featured_local_published ON public.post_aggregates USING btree (featured_local DESC, published DESC);
  45
  46 CREATE INDEX idx_post_aggregates_featured_community_published ON public.post_aggregates USING btree (featured_community DESC, published DESC);
  47
  48 CREATE INDEX idx_post_aggregates_featured_local_active ON public.post_aggregates USING btree (featured_local DESC, hot_rank ((score)::numeric, newest_comment_time_necro) DESC, newest_comment_time_necro DESC);
  49
  50 CREATE INDEX idx_post_aggregates_featured_community_active ON public.post_aggregates USING btree (featured_community DESC, hot_rank ((score)::numeric, newest_comment_time_necro) DESC, newest_comment_time_necro DESC);
  51
  52 CREATE INDEX idx_comment_aggregates_hot ON public.comment_aggregates USING btree (hot_rank ((score)::numeric, published) DESC, published DESC);
  53
  54 CREATE INDEX idx_community_aggregates_hot ON public.community_aggregates USING btree (hot_rank ((subscribers)::numeric, published) DESC, published DESC);
  55