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

   1 -- Drop the old indexes
   2 drop index idx_post_aggregates_featured_local_hot;
   3 drop index idx_post_aggregates_featured_local_active;
   4 drop index idx_post_aggregates_featured_local_score;
   5 drop index idx_post_aggregates_featured_community_hot;
   6 drop index idx_post_aggregates_featured_community_active;
   7 drop index idx_post_aggregates_featured_community_score;
   8 drop index idx_comment_aggregates_hot;
   9 drop index idx_comment_aggregates_score;
  10
  11 -- Add a published desc, to the end of the hot and active ranks
  12
  13 -- Add missing most comments index
  14 create index idx_post_aggregates_featured_local_most_comments on post_aggregates (featured_local desc, comments desc, published desc);
  15 create index idx_post_aggregates_featured_community_most_comments on post_aggregates (featured_community desc, comments desc, published desc);
  16
  17 -- featured_local
  18 create index idx_post_aggregates_featured_local_hot on post_aggregates (featured_local desc, hot_rank desc, published desc);
  19 create index idx_post_aggregates_featured_local_active on post_aggregates (featured_local desc, hot_rank_active desc, published desc);
  20 create index idx_post_aggregates_featured_local_score on post_aggregates (featured_local desc, score desc, published desc);
  21
  22 -- featured_community
  23 create index idx_post_aggregates_featured_community_hot on post_aggregates (featured_community desc, hot_rank desc, published desc);
  24 create index idx_post_aggregates_featured_community_active on post_aggregates (featured_community desc, hot_rank_active desc, published desc);
  25 create index idx_post_aggregates_featured_community_score on post_aggregates (featured_community desc, score desc, published desc);
  26
  27 -- Fixing some comment aggregates ones
  28 create index idx_comment_aggregates_hot on comment_aggregates (hot_rank desc, published desc);
  29 create index idx_comment_aggregates_score on comment_aggregates (score desc, published desc);
  30