migrations/2023-07-04-153335_add_optimized_indexes/up.sql

   1 -- Create an admin person index
   2 create index if not exists idx_person_admin on person (admin);
   3
   4 -- Compound indexes, using featured_, then the other sorts, proved to be much faster
   5 -- Drop the old indexes
   6 drop index idx_post_aggregates_score;
   7 drop index idx_post_aggregates_published;
   8 drop index idx_post_aggregates_newest_comment_time;
   9 drop index idx_post_aggregates_newest_comment_time_necro;
  10 drop index idx_post_aggregates_featured_community;
  11 drop index idx_post_aggregates_featured_local;
  12 drop index idx_post_aggregates_hot;
  13 drop index idx_post_aggregates_active;
  14
  15 -- featured_local
  16 create index idx_post_aggregates_featured_local_score on post_aggregates (featured_local desc, score desc);
  17 create index idx_post_aggregates_featured_local_newest_comment_time on post_aggregates (featured_local desc, newest_comment_time desc);
  18 create index idx_post_aggregates_featured_local_newest_comment_time_necro on post_aggregates (featured_local desc, newest_comment_time_necro desc);
  19 create index idx_post_aggregates_featured_local_hot on post_aggregates (featured_local desc, hot_rank desc);
  20 create index idx_post_aggregates_featured_local_active on post_aggregates (featured_local desc, hot_rank_active desc);
  21 create index idx_post_aggregates_featured_local_published on post_aggregates (featured_local desc, published desc);
  22 create index idx_post_aggregates_published on post_aggregates (published desc);
  23
  24 -- featured_community
  25 create index idx_post_aggregates_featured_community_score on post_aggregates (featured_community desc, score desc);
  26 create index idx_post_aggregates_featured_community_newest_comment_time on post_aggregates (featured_community desc, newest_comment_time desc);
  27 create index idx_post_aggregates_featured_community_newest_comment_time_necro on post_aggregates (featured_community desc, newest_comment_time_necro desc);
  28 create index idx_post_aggregates_featured_community_hot on post_aggregates (featured_community desc, hot_rank desc);
  29 create index idx_post_aggregates_featured_community_active on post_aggregates (featured_community desc, hot_rank_active desc);
  30 create index idx_post_aggregates_featured_community_published on post_aggregates (featured_community desc, published desc);
  31
  32