migrations/2023-07-14-154840_add_optimized_indexes_published/down.sql

   1 -- Drop the new indexes
   2 drop index idx_post_aggregates_featured_local_most_comments;
   3 drop index idx_post_aggregates_featured_local_hot;
   4 drop index idx_post_aggregates_featured_local_active;
   5 drop index idx_post_aggregates_featured_local_score;
   6 drop index idx_post_aggregates_featured_community_hot;
   7 drop index idx_post_aggregates_featured_community_active;
   8 drop index idx_post_aggregates_featured_community_score;
   9 drop index idx_post_aggregates_featured_community_most_comments;
  10 drop index idx_comment_aggregates_hot;
  11 drop index idx_comment_aggregates_score;
  12
  13 -- Add the old ones back in
  14 -- featured_local
  15 create index idx_post_aggregates_featured_local_hot on post_aggregates (featured_local desc, hot_rank desc);
  16 create index idx_post_aggregates_featured_local_active on post_aggregates (featured_local desc, hot_rank_active desc);
  17 create index idx_post_aggregates_featured_local_score on post_aggregates (featured_local desc, score desc);
  18
  19 -- featured_community
  20 create index idx_post_aggregates_featured_community_hot on post_aggregates (featured_community desc, hot_rank desc);
  21 create index idx_post_aggregates_featured_community_active on post_aggregates (featured_community desc, hot_rank_active desc);
  22 create index idx_post_aggregates_featured_community_score on post_aggregates (featured_community desc, score desc);
  23
  24 create index idx_comment_aggregates_hot on comment_aggregates (hot_rank desc);
  25 create index idx_comment_aggregates_score on comment_aggregates (score desc);
  26